Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Since Oracle 11g version, Data Pump allows to compress the backup before writing to dump file with “compression” parameter.
The Compression parameter takes 4 values. These :
ALL: Data and metadata information is compressed.
DATA_ONLY: Only data is compressed.
METADATA_ONLY: Only the metadata information is compressed.
NONE: No compression is done. This is the default value.
In order to use the compression feature in the Data Pump, the database compatible parameter must be at least 11.0.0. The compatible parameter can be 10.2 for METADATA_ONLY. For use as ALL or DATA_ONLY, your database must be Enterprise Edition. Oracle Advanced Compression cannot be used with Standard Edition One (SE2).
The use is as follows.
1 | expdp test/test compression=all directory=export dumpfile=test.dmp |
If you choose “ALL”, the backup file size can be reduced by up to 10 times. But the export time will increase significantly. Importing from a compressed backup will also significantly increase the import time.
In addition, the Advanced Compression Option license is required for using the compression option. If you use compression, the number of COMPRESSCNT returned from the following query increases. This way you can determine whether you are using this feature.
1 2 3 4 5 | SQL> select utlname, COMPRESSCNT from ku_utluse where utlname = 'Oracle Utility Datapump (Export)'; UTLNAME COMPRESSCNT -------------------------------------------------- ---------- Oracle Utility Datapump (Export) 597 |
