A new “compression algorithm” parameter has been introduced in 12c release for export datapump utility, the parameter is: COMPRESSION_ALGORITHM .
There are three modes for this new compression: basic, low, medium, high
The default mode is “basic”.
** Here i am going to explore exporting the same schema in 3 different ways for testing purpose from a 12c database:
Exporting the schema using datapump command with “no compression”:
$ORACLE_HOME/bin/expdp “‘/as sysdba ‘” DIRECTORY=DUMP_DIR SCHEMAS=TEST_SCHEMA DUMPFILE=DUMP_NO_COMPRESSION.dmp logfile=DUMP_NO_COMPRESSION_EXP.log logtime=all
Exporting the schema using datapump command using the new compression parameter in “HIGHT” mode:
$ORACLE_HOME/bin/expdp “‘/as sysdba ‘” DIRECTORY= DUMP_DIR SCHEMAS= TEST_SCHEMA DUMPFILE=DUMP_HIGHT_COMPRESSION.dmp COMPRESSION=ALL COMPRESSION_ALGORITHM=HIGH logfile=DUMP_HIGH_COMPRESSION_EXP.log logtime=all
Exportng the schema using datapump command using the new compression parameter in “MEDUIM” mode:
$ORACLE_HOME/bin/expdp “‘/as sysdba ‘” DIRECTORY= DUMP_DIR SCHEMAS= TEST_SCHEMA DUMPFILE=DUMP_MEDIUM_COMPRESSION.dmp COMPRESSION=ALL COMPRESSION_ALGORITHM=MEDIUM logfile=DUMP_MEDIUM_COMPRESSION_EXP.log logtime=all
The conclusion is the following:
- Dump file generated with “no compression” size for the schema was around 13.41 GB
- Dump file generated using the “high compression” size was around 6.88 GB
- Dump file generated using “medium compression” size was around 7.31 GB
Important remark: the normal export took around 7 minutes to finish however the highly compressed export process took around 1 hour !!!
While using medium compression algorithm it took 16 minutes.
This was performed on IBM AIX environment for Oracle 22.214.171.124, of course the “server resources” is a factor while performing compression in addition to the “data types” of the schema exported is another factor to be considered.