【EXPDP】11g版本EXPDP 的COMPRESSION引數壓縮比堪比“gzip -9”
這個壓縮比例可以和作業系統“gzip -9”相媲美，某些特例下有可能比gzip還要高效。體驗之，供參考。
1.Oracle 11g中expdp幫助頁中關於COMPRESSION引數的描述
secooler@secDB /home/oracle$ expdp -help
……
COMPRESSION
Reduce the size of a dump file.
Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE.
……
可見，11g中的COMPRESSION引數提供四個選項，分別是ALL、DATA_ONLY、METADATA_ONLY和NONE，非常的豐富，稍後我們將使用ALL引數進行操作。
2.Oracle 10g中關於COMPRESSION引數的描述如下
Keyword Description (Default)
---------------------------------------------------------------------
……
COMPRESSION Reduce size of dumpfile contents where valid
keyword values are: (METADATA_ONLY) and NONE.
……
10g中的COMPRESSION引數只提供METADATA_ONLY和NONE兩個選項，基本上沒有提供壓縮功能。
3.使用11g EXPDP工具的四個壓縮引數分別生成四個dump檔案
1）使用ALL引數
secooler@secDB /expdp$ expdp sec/sec dumpfile=sec_ALL.dmp logfile=sec_ALL.log tables=t directory=dump_dir compression=ALL
Export: Release 11.2.0.1.0 - Production on Sat Mar 20 22:48:52 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Starting "SEC"."SYS_EXPORT_TABLE_01": sec/******** dumpfile=sec_ALL.dmp logfile=sec_ALL.log tables=t directory=dump_dir compression=ALL
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 136 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SEC"."T" 14.26 MB 1155520 rows
Master table "SEC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_TABLE_01 is:
/expdp/sec_ALL.dmp
Job "SEC"."SYS_EXPORT_TABLE_01" successfully completed at 22:49:08
2）使用DATA_ONLY引數
secooler@secDB /expdp$ expdp sec/sec dumpfile=sec_DATA_ONLY.dmp logfile=sec_DATA_ONLY.log tables=t directory=dump_dir compression=DATA_ONLY
Export: Release 11.2.0.1.0 - Production on Sat Mar 20 22:49:32 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Starting "SEC"."SYS_EXPORT_TABLE_01": sec/******** dumpfile=sec_DATA_ONLY.dmp logfile=sec_DATA_ONLY.log tables=t directory=dump_dir compression=DATA_ONLY
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 136 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SEC"."T" 14.26 MB 1155520 rows
Master table "SEC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_TABLE_01 is:
/expdp/sec_DATA_ONLY.dmp
Job "SEC"."SYS_EXPORT_TABLE_01" successfully completed at 22:49:48
3）使用METADATA_ONLY引數
secooler@secDB /expdp$ expdp sec/sec dumpfile=sec_METADATA_ONLY.dmp logfile=sec_METADATA_ONLY.log tables=t directory=dump_dir compression=METADATA_ONLY
Export: Release 11.2.0.1.0 - Production on Sat Mar 20 22:50:16 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Starting "SEC"."SYS_EXPORT_TABLE_01": sec/******** dumpfile=sec_METADATA_ONLY.dmp logfile=sec_METADATA_ONLY.log tables=t directory=dump_dir compression=METADATA_ONLY
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 136 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SEC"."T" 111.7 MB 1155520 rows
Master table "SEC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_TABLE_01 is:
/expdp/sec_METADATA_ONLY.dmp
Job "SEC"."SYS_EXPORT_TABLE_01" successfully completed at 22:50:27
4）使用NONE引數
secooler@secDB /expdp$ expdp sec/sec dumpfile=sec_NONE.dmp logfile=sec_NONE.log tables=t directory=dump_dir compression=NONE
Export: Release 11.2.0.1.0 - Production on Sat Mar 20 22:50:43 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Starting "SEC"."SYS_EXPORT_TABLE_01": sec/******** dumpfile=sec_NONE.dmp logfile=sec_NONE.log tables=t directory=dump_dir compression=NONE
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 136 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SEC"."T" 111.7 MB 1155520 rows
Master table "SEC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_TABLE_01 is:
/expdp/sec_NONE.dmp
Job "SEC"."SYS_EXPORT_TABLE_01" successfully completed at 22:50:53
4.比較生成的四個dump檔案大小
secooler@secDB /expdp$ du -sm sec*.dmp
15 sec_ALL.dmp
15 sec_DATA_ONLY.dmp
112 sec_METADATA_ONLY.dmp
112 sec_NONE.dmp
secooler@secDB /expdp$ du -sb sec*.dmp
14987264 sec_ALL.dmp
14987264 sec_DATA_ONLY.dmp
117223424 sec_METADATA_ONLY.dmp
117223424 sec_NONE.dmp
實驗表明，使用ALL引數和DATA_ONLY引數生成的備份檔案基本一樣大；使用METADATA_ONLY引數與NONE引數效果一樣。
使用壓縮與不進行壓縮的比率是：15/112約等於1/7！
這才是真正的壓縮。
5.使用“gzip -9”對未壓縮的備份檔案進行壓縮，做一下比較
secooler@secDB /expdp$ gzip -9 sec_NONE.dmp
secooler@secDB /expdp$ du -sm sec*dmp*
15 sec_ALL.dmp
15 sec_DATA_ONLY.dmp
112 sec_METADATA_ONLY.dmp
15 sec_NONE.dmp.gz
secooler@secDB /expdp$ du -sb sec*dmp*
14987264 sec_ALL.dmp
14987264 sec_DATA_ONLY.dmp
117223424 sec_METADATA_ONLY.dmp
14717055 sec_NONE.dmp.gz
14987264與14717055的區別，相差甚微，基本相同，“gzip -9”稍勝一點點（也許在您的具體環境下“gzip -9”會遜色一點點）。
6.小結
Oracle 11g的EXPDP工具提供了真正意義上的“備份壓縮”，這個技術在備份空間不足的情況下非常實用。
Oracle在改進和最佳化中不斷的前進，慢慢體會吧。
Good luck.
secooler
10.03.20
1.Oracle 11g中expdp幫助頁中關於COMPRESSION引數的描述
secooler@secDB /home/oracle$ expdp -help
……
COMPRESSION
Reduce the size of a dump file.
Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE.
……
可見，11g中的COMPRESSION引數提供四個選項，分別是ALL、DATA_ONLY、METADATA_ONLY和NONE，非常的豐富，稍後我們將使用ALL引數進行操作。
2.Oracle 10g中關於COMPRESSION引數的描述如下
Keyword Description (Default)
---------------------------------------------------------------------
……
COMPRESSION Reduce size of dumpfile contents where valid
keyword values are: (METADATA_ONLY) and NONE.
……
10g中的COMPRESSION引數只提供METADATA_ONLY和NONE兩個選項，基本上沒有提供壓縮功能。
3.使用11g EXPDP工具的四個壓縮引數分別生成四個dump檔案
1）使用ALL引數
secooler@secDB /expdp$ expdp sec/sec dumpfile=sec_ALL.dmp logfile=sec_ALL.log tables=t directory=dump_dir compression=ALL
Export: Release 11.2.0.1.0 - Production on Sat Mar 20 22:48:52 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Starting "SEC"."SYS_EXPORT_TABLE_01": sec/******** dumpfile=sec_ALL.dmp logfile=sec_ALL.log tables=t directory=dump_dir compression=ALL
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 136 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SEC"."T" 14.26 MB 1155520 rows
Master table "SEC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_TABLE_01 is:
/expdp/sec_ALL.dmp
Job "SEC"."SYS_EXPORT_TABLE_01" successfully completed at 22:49:08
2）使用DATA_ONLY引數
secooler@secDB /expdp$ expdp sec/sec dumpfile=sec_DATA_ONLY.dmp logfile=sec_DATA_ONLY.log tables=t directory=dump_dir compression=DATA_ONLY
Export: Release 11.2.0.1.0 - Production on Sat Mar 20 22:49:32 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Starting "SEC"."SYS_EXPORT_TABLE_01": sec/******** dumpfile=sec_DATA_ONLY.dmp logfile=sec_DATA_ONLY.log tables=t directory=dump_dir compression=DATA_ONLY
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 136 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SEC"."T" 14.26 MB 1155520 rows
Master table "SEC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_TABLE_01 is:
/expdp/sec_DATA_ONLY.dmp
Job "SEC"."SYS_EXPORT_TABLE_01" successfully completed at 22:49:48
3）使用METADATA_ONLY引數
secooler@secDB /expdp$ expdp sec/sec dumpfile=sec_METADATA_ONLY.dmp logfile=sec_METADATA_ONLY.log tables=t directory=dump_dir compression=METADATA_ONLY
Export: Release 11.2.0.1.0 - Production on Sat Mar 20 22:50:16 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Starting "SEC"."SYS_EXPORT_TABLE_01": sec/******** dumpfile=sec_METADATA_ONLY.dmp logfile=sec_METADATA_ONLY.log tables=t directory=dump_dir compression=METADATA_ONLY
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 136 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SEC"."T" 111.7 MB 1155520 rows
Master table "SEC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_TABLE_01 is:
/expdp/sec_METADATA_ONLY.dmp
Job "SEC"."SYS_EXPORT_TABLE_01" successfully completed at 22:50:27
4）使用NONE引數
secooler@secDB /expdp$ expdp sec/sec dumpfile=sec_NONE.dmp logfile=sec_NONE.log tables=t directory=dump_dir compression=NONE
Export: Release 11.2.0.1.0 - Production on Sat Mar 20 22:50:43 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Starting "SEC"."SYS_EXPORT_TABLE_01": sec/******** dumpfile=sec_NONE.dmp logfile=sec_NONE.log tables=t directory=dump_dir compression=NONE
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 136 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SEC"."T" 111.7 MB 1155520 rows
Master table "SEC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_TABLE_01 is:
/expdp/sec_NONE.dmp
Job "SEC"."SYS_EXPORT_TABLE_01" successfully completed at 22:50:53
4.比較生成的四個dump檔案大小
secooler@secDB /expdp$ du -sm sec*.dmp
15 sec_ALL.dmp
15 sec_DATA_ONLY.dmp
112 sec_METADATA_ONLY.dmp
112 sec_NONE.dmp
secooler@secDB /expdp$ du -sb sec*.dmp
14987264 sec_ALL.dmp
14987264 sec_DATA_ONLY.dmp
117223424 sec_METADATA_ONLY.dmp
117223424 sec_NONE.dmp
實驗表明，使用ALL引數和DATA_ONLY引數生成的備份檔案基本一樣大；使用METADATA_ONLY引數與NONE引數效果一樣。
使用壓縮與不進行壓縮的比率是：15/112約等於1/7！
這才是真正的壓縮。
5.使用“gzip -9”對未壓縮的備份檔案進行壓縮，做一下比較
secooler@secDB /expdp$ gzip -9 sec_NONE.dmp
secooler@secDB /expdp$ du -sm sec*dmp*
15 sec_ALL.dmp
15 sec_DATA_ONLY.dmp
112 sec_METADATA_ONLY.dmp
15 sec_NONE.dmp.gz
secooler@secDB /expdp$ du -sb sec*dmp*
14987264 sec_ALL.dmp
14987264 sec_DATA_ONLY.dmp
117223424 sec_METADATA_ONLY.dmp
14717055 sec_NONE.dmp.gz
14987264與14717055的區別，相差甚微，基本相同，“gzip -9”稍勝一點點（也許在您的具體環境下“gzip -9”會遜色一點點）。
6.小結
Oracle 11g的EXPDP工具提供了真正意義上的“備份壓縮”，這個技術在備份空間不足的情況下非常實用。
Oracle在改進和最佳化中不斷的前進，慢慢體會吧。
Good luck.
secooler
10.03.20
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/21374452/viewspace-2135208/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 10G、11G expdp的compression引數的區別
- Oracle——EXPDP加密和壓縮Oracle加密
- Java和.NET的GZIP壓縮功能對比Java
- EXPDP的parallel引數Parallel
- Nginx的gzip壓縮的原理和設定引數Nginx
- oracle10g,11g中的exp,expdp引數compress, compression完全不同的定義Oracle
- Oracle expdp 排除表引數Oracle
- 【EXPDP】不使用DIRECTORY引數完成expdp資料備份
- 【EXPDP】使用expdp的QUERY引數限定備份資料的範圍
- expdp/impdp 使用version引數跨版本資料遷移
- 啟用 Brotli 壓縮演算法，對比 Gzip 壓縮 CDN 流量再減少 20%演算法
- 【EXPDP】使用EXPDP備份資料時預估大小——ESTIMATE引數
- Oracle expdp impdp dump引數介紹Oracle
- 工具新特性1－expdp 壓縮dump檔案集
- Oracle的expdp/impdp工具和exp/imp工具比較Oracle
- EXPDP/IMPDP 中的並行度PARALLEL引數並行Parallel
- 關於Java的GZIP壓縮與.net C#的GZIP壓縮的差異JavaC#
- Is it possible to use EXPDP directly with GZIP ? [ID 463336.1]
- 關於 Oracle 10g EXPDP 的 EXCLUDE 引數Oracle 10g
- Oracle 10g expdp attach引數體驗Oracle 10g
- Apache 開啟gzip壓縮Apache
- 使用gzip壓縮檔案
- 堪比JMeter的.Net壓測工具 - Crank 入門篇JMeter
- Linux壓縮工具的效能比較Linux
- 大資料中批次壓縮與獨立壓縮的比較 - Bozho大資料
- Oracle Index Key Compression索引壓縮OracleIndex索引
- 【Data Pump】理解expdp中的ESTIMATE和ESTIMATE_ONLY引數
- linux下gzip的壓縮詳解Linux
- 各壓縮演算法對比演算法
- 給Tomcat，Apache配置gzip壓縮(HTTP壓縮)功能 (轉)TomcatApacheHTTP
- gulp-imagemin版本9圖片壓縮
- 使用Exp和Expdp匯出資料的效能對比與優化優化
- 【EXPDP】運用PARALLEL 和FILESIZE引數提高備份效率Parallel
- Vue開啟gzip壓縮檔案Vue
- Apache開啟GZIP壓縮功能方法Apache
- vue-cli3.0配置GZIP壓縮Vue
- 高效能 gzip 壓縮工具 pgzip
- nginx指定埠開啟gzip壓縮Nginx