爱站程序员基地[code]关于Oracle数据库数据泵的使用案例，详细介绍在Oracle数据泵导出时对数据进行压缩处理的全过程。COMPRESSION 压缩参数说明在10g中，数据泵的压缩只支持METADATA_ONLY这一种方式，在Oracle 11g中新增了几种压缩方法。ALL ：对导出的元数据和表数据都进行压缩，得到的导出文件是最小的，耗时也是最长的。DATA_ONLY ：仅对表数据进行压缩，对于大数据量的导出效果明显，会比METADATA_ONLY方式得到更小的压缩文件。METADATA_ONLY ：仅对元数据进行压缩，而不会对表数据进行压缩，这种压缩执行后效果一般不是很明显，不过速度比较快。NONE ：不进行任何的压缩，导出后的文件也是最大的。DEFAULT ：默认方式，即不指定COMPRESSION参数，会采用默认的压缩方式METADATA_ONLY。1. ALL压缩方式导出数据[oracle@rac3 ~]$ expdp hcxiang/willnice directory=HCXBAK dumpfile=bt02_all.dump tables=BT02 compression=allExport: Release 11.2.0.4.0 – Production on Wed May 20 10:42:36 2015Copyright (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 ProductionWiOracle???????oracleplus.netth the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting \"HCXIANG\".\"SYS_EXPORT_TABLE_01\": hcxiang/******** directory=HCXBAK dumpfile=bt02_all.dump tables=BT02 compression=allEstimate in progress using BLOCKS method…Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 1.250 GBProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported \"HCXIANG\".\"BT02\" 119.5 MB 11139840 rowsMaster 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.dumpJob \"HCXIANG\".\"SYS_EXPORT_TABLE_01\" successfully completed at Wed May 20 10:43:15 2015 elapsed 0 00:00:382. DATA_ONLY压缩方式导出数据[oracle@rac3 ~]$ expdp hcxiang/willnice directory=HCXBAK dumpfile=bt02_dataonly.dump tables=BT02 compression=data_onlyExport: Release 11.2.0.4.0 – Production on Wed May 20 10:44:45 2015Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting \"HCXIANG\".\"SYS_EXPORT_TABLE_01\": hcxiang/******** directory=HCXBAK dumpfile=bt02_dataonly.dump tables=BT02 compression=data_onlyEstimate in progress using BLOCKS method…Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 1.250 GBProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported \"HCXIANG\".\"BT02\" 119.5 MB 11139840 rowsMaster 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.dumpJob \"HCXIANG\".\"SYS_EXPORT_TABLE_01\" successfully completed at Wed May 20 10:45:15 2015 elapsed 0 00:00:293. METADATA_ONLY压缩方式导出数据[oracle@rac3 ~]$ expdp hcxiang/willnice directory=HCXBAK dumpfile=bt02_metadataonly.dump tables=BT02 compression=metadata_onlyExport: Release 11.2.0.4.0 – Production on Wed May 20 10:47:02 2015Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting \"HCXIANG\".\"SYS_EXPORT_TABLE_01\": hcxiang/******** directory=HCXBAK dumpfile=bt02_metadataonly.dump tables=BT02 compression=metadata_onlyEstimate in progress using BLOCKS method…Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 1.250 GBProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported \"HCXIANG\".\"BT02\" 1.034 GB 11139840 rowsMaster 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.dumpJob \"HCXIANG\".\"SYS_EXPORT_TABLE_01\" successfully completed at Wed May 20 10:47:16 2015 elapsed 0 00:00:134. NONE压缩方式导出数据[oracle@rac3 ~]$ expdp hcxiang/willnice directory=HCXBAK dumpfile=bt02_none.dump tables=BT02 compression=noneExport: Release 11.2.0.4.0 – Production on Wed May 20 10:49:05 2015Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting \"HCXIANG\".\"SYS_EXPORT_TABLE_01\": hcxiang/******** directory=HCXBAK dumpfile=bt02_none.dump tables=BT02 compression=noneEstimate in progress using BLOCKS method…Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 1.250 GBProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported \"HCXIANG\".\"BT02\" 1.034 GB 11139840 rowsMaster 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.dumpJob \"HCXIANG\".\"SYS_EXPORT_TABLE_01\" successfully completed at Wed May 20 10:49:18 2015 elapsed 0 00:00:125. DEFAULT压缩方式导出数据[oracle@rac3 ~]$ expdp hcxiang/willnice directory=HCXBAK dumpfile=bt02_default.dump tables=BT02Export: Release 11.2.0.4.0 – Production on Wed May 20 10:50:24 2015Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting \"HCXIANG\".\"SYS_EXPORT_TABLE_01\": hcxiang/******** directory=HCXBAK dumpfile=bt02_default.dump tables=BT02Estimate in progress using BLOCKS method…Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 1.250 GBProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported \"HCXIANG\".\"BT02\" 1.034 GB 11139840 rowsMaster 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.dumpJob \"HCXIANG\".\"SYS_EXPORT_TABLE_01\" successfully completed at Wed May 20 10:50:36 2015 elapsed 0 00:00:12比较几种压缩方式导出的文件大小[oracle@rac3 backup]$ ll -ltotal 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.oracleplus.net/arch/1180.html
