The COMPRESSION parameter COMPRESSION ratio of Oracle 11g EXPDP is comparable to that of "gzip-9"

The COMPRESSION parameter COMPRESSION ratio of Oracle 11g EXPDP is comparable to that of "gzip-9"

This compression ratio can be comparable to the operating system "gzip-9". In some cases, it may be more efficient than gzip. For your reference.

1. COMPRESSION parameter description on the expdp help page in Oracle 11g
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.
......

It can be seen that the COMPRESSION parameter in the 11g provides four options: ALL, DATA_ONLY, METADATA_ONLY, and NONE, which are very rich. We will use the ALL parameter for operations later.

2. The COMPRESSION parameter in Oracle 10 Gb is described as follows:
Keyword Description (Default)
---------------------------------------------------------------------
......
COMPRESSION Reduce size of dumpfile contents where valid
Keyword values are: (METADATA_ONLY) and NONE.
......
The COMPRESSION parameter in 10 Gb only provides the METADATA_ONLY and NONE options, and basically does not provide the COMPRESSION function.

3. Use the four Compression Parameters of the 11g EXPDP tool to generate four dump files respectively.
1) Use the ALL Parameter
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,200 9, 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
.. 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) use the DATA_ONLY Parameter
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,200 9, 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
.. 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) use the METADATA_ONLY Parameter
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,200 9, 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
.. 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) use the NONE Parameter
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,200 9, 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
.. 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. Compare the size of the four dump files generated
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

The experiment shows that the backup file generated using the ALL parameter and the DATA_ONLY parameter is basically the same size; the METADATA_ONLY parameter works the same as the NONE parameter.

The ratio of compression to non-compression is: 15/112 or about 1/7!
This is the real compression.

5. Use "gzip-9" to compress uncompressed Backup files and make a comparison.
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

The difference between 14987264 and 14717055 is very small, basically the same. "gzip-9" is a little better (maybe "gzip-9" is a little inferior in your specific environment ).

6. Summary
Oracle 11g EXPDP provides true "backup compression", which is very useful when the backup space is insufficient.
Oracle is constantly improving and optimizing.

---------------------------- Lili split line ----------------------------

Oracle Import and Export expdp IMPDP details

Solution to Oracle 10g expdp export error ORA-4031

Oracle 10gr2 rac expdp error UDE-00008 ORA-31626

Use of expdp/impdp to back up databases in Oracle

Oracle backup recovery (expdp/impdp)