作者:huangtingzhong 发布:2015-05-20 16:11 分类:
MIGRATE 阅读:576 views
抢沙发
在10g中,数据泵的压缩只支持METADATA_ONLY这一种方式,在Oracle 11g中新增了几种压缩方法。
- ALL 对导出的元数据和表数据都进行压缩,得到的导出文件是最小的,耗时也是最长的。
- DATA_ONLY 仅对表数据进行压缩,对于大数据量的导出效果明显,会比METADATA_ONLY方式得到更小的压缩文件。
- METADATA_ONLY 仅对元数据进行压缩,而不会对表数据进行压缩,这种压缩执行后效果一般不是很明显,不过速度比较快。
- NONE 不进行任何的压缩,导出后的文件也是最大的。
- DEFAULT 默认方式,即不指定COMPRESSION参数,会采用默认的压缩方式METADATA_ONLY。
ALL压缩方式导出数据
[oracle@rac3 ~]$ expdp hcxiang/willnice directory=HCXBAK dumpfile=bt02_all.dump tables=BT02 compression=all
Export: Release 11.2.0.4.0 – Production on Wed May 20 10:42:36 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "HCXIANG"."SYS_EXPORT_TABLE_01": hcxiang/******** directory=HCXBAK dumpfile=bt02_all.dump tables=BT02 compression=all Estimate in progress using BLOCKS method… Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 1.250 GB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "HCXIANG"."BT02" 119.5 MB 11139840 rows Master table "HCXIANG"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for HCXIANG.SYS_EXPORT_TABLE_01 is: /u01/app/oracle/backup/bt02_all.dump Job "HCXIANG"."SYS_EXPORT_TABLE_01" successfully completed at Wed May 20 10:43:15 2015 elapsed 0 00:00:38
|
DATA_ONLY压缩方式导出数据
[oracle@rac3 ~]$ expdp hcxiang/willnice directory=HCXBAK dumpfile=bt02_dataonly.dump tables=BT02 compression=data_only
Export: Release 11.2.0.4.0 – Production on Wed May 20 10:44:45 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "HCXIANG"."SYS_EXPORT_TABLE_01": hcxiang/******** directory=HCXBAK dumpfile=bt02_dataonly.dump tables=BT02 compression=data_only Estimate in progress using BLOCKS method… Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 1.250 GB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "HCXIANG"."BT02" 119.5 MB 11139840 rows Master table "HCXIANG"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for HCXIANG.SYS_EXPORT_TABLE_01 is: /u01/app/oracle/backup/bt02_dataonly.dump Job "HCXIANG"."SYS_EXPORT_TABLE_01" successfully completed at Wed May 20 10:45:15 2015 elapsed 0 00:00:29
|
METADATA_ONLY压缩方式导出数据
[oracle@rac3 ~]$ expdp hcxiang/willnice directory=HCXBAK dumpfile=bt02_metadataonly.dump tables=BT02 compression=metadata_only
Export: Release 11.2.0.4.0 – Production on Wed May 20 10:47:02 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "HCXIANG"."SYS_EXPORT_TABLE_01": hcxiang/******** directory=HCXBAK dumpfile=bt02_metadataonly.dump tables=BT02 compression=metadata_only Estimate in progress using BLOCKS method… Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 1.250 GB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "HCXIANG"."BT02" 1.034 GB 11139840 rows Master table "HCXIANG"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for HCXIANG.SYS_EXPORT_TABLE_01 is: /u01/app/oracle/backup/bt02_metadataonly.dump Job "HCXIANG"."SYS_EXPORT_TABLE_01" successfully completed at Wed May 20 10:47:16 2015 elapsed 0 00:00:13
|
NONE压缩方式导出数据
[oracle@rac3 ~]$ expdp hcxiang/willnice directory=HCXBAK dumpfile=bt02_none.dump tables=BT02 compression=none
Export: Release 11.2.0.4.0 – Production on Wed May 20 10:49:05 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "HCXIANG"."SYS_EXPORT_TABLE_01": hcxiang/******** directory=HCXBAK dumpfile=bt02_none.dump tables=BT02 compression=none Estimate in progress using BLOCKS method… Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 1.250 GB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "HCXIANG"."BT02" 1.034 GB 11139840 rows Master table "HCXIANG"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for HCXIANG.SYS_EXPORT_TABLE_01 is: /u01/app/oracle/backup/bt02_none.dump Job "HCXIANG"."SYS_EXPORT_TABLE_01" successfully completed at Wed May 20 10:49:18 2015 elapsed 0 00:00:12
|
DEFAULT压缩方式导出数据
[oracle@rac3 ~]$ expdp hcxiang/willnice directory=HCXBAK dumpfile=bt02_default.dump tables=BT02
Export: Release 11.2.0.4.0 – Production on Wed May 20 10:50:24 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "HCXIANG"."SYS_EXPORT_TABLE_01": hcxiang/******** directory=HCXBAK dumpfile=bt02_default.dump tables=BT02 Estimate in progress using BLOCKS method… Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 1.250 GB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "HCXIANG"."BT02" 1.034 GB 11139840 rows Master table "HCXIANG"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for HCXIANG.SYS_EXPORT_TABLE_01 is: /u01/app/oracle/backup/bt02_default.dump Job "HCXIANG"."SYS_EXPORT_TABLE_01" successfully completed at Wed May 20 10:50:36 2015 elapsed 0 00:00:12
|
比较几种压缩方式导出的文件大小
[oracle@rac3 backup]$ ll -l total 3497916 -rw-r—– 1 oracle oinstall 125358080 May 20 10:43 bt02_all.dump -rw-r—– 1 oracle oinstall 125366272 May 20 10:45 bt02_dataonly.dump -rw-r—– 1 oracle oinstall 1110372352 May 20 10:50 bt02_default.dump -rw-r—– 1 oracle oinstall 1110372352 May 20 10:47 bt02_metadataonly.dump -rw-r—– 1 oracle oinstall 1110380544 May 20 10:49 bt02_none.dump
|
可以看到对于ALL压缩方式,将1.2G的导出文件压缩到了120M,压缩比达到10%,效果还是比较不错的。
本文固定链接: http://www.htz.pw/?p=1073 | 认真就输
最活跃的读者