COMPRESSION parameter is used with EXPDP, to compress the generated dump file.

NOTE – For using compression parameter with datapump, we need to have Oracle advance compression license.

 

COMPRESSION
Reduce the size of a dump file.
Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE.

In this below demo, we will compare the dump size with or without compression parameter.

 

Get the DUMPSIZE without COMPRESSION parameter :

cat exp_compress.par

dumpfile=dbaclass_wo_compression.dmp
logfile=dbaclass.log
directory=EXPDIR
tables=DBACLASS.OBJECT_LIST

 expdp parfile=exp_compress.par

Export: Release 12.1.0.2.0 - Production on Mon Nov 19 12:56:14 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA parfile=exp_compress.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 29 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "DBACLASS"."OBJECT_LIST"                    24.69 MB  219456 rows ---- >>> 25 MB AROUND
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /export/home/oracle/dbaclass__compression.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Mon Nov 19 12:56:19 2018 elapsed 0 00:00:03

So, without compression, the size is around 25 MB. Let’s do the export with compression.

EXPORT WITH COMPRESSION=ALL

 cat exp_compress.par

dumpfile=dbaclass_compression.dmp
logfile=dbaclass.log
directory=EXPDIR
tables=DBACLASS.OBJECT_LIST
compression=ALL
 expdp parfile=exp_compress.par

Export: Release 12.1.0.2.0 - Production on Mon Nov 19 12:56:48 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA parfile=exp_compress.par
Estimate in progress using BLOCKS method...
 Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 29 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "DBACLASS"."OBJECT_LIST"                    2.922 MB  219456 rows --- >> 
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /export/home/oracle/dbaclass_compression.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Mon Nov 19 12:56:56 2018 elapsed 0 00:00:05

We can see, from 25 MB ,it came down to 2 MB :). It really saves a lot of disk space.

However, there is a tradeoff. Use of compression will increase the time required for the export job.

 

W/O COMPRESSION WITH COMPRESSION
SPACE REQUIRED 25mb 3 mb
TIME TAKEN 3 second 5 second
CPU USAGE NORMAL bit more than w/o compression
LICENSE? No additional license Advance compression license required

 

COMPRESSION_ALGORITHM:

We can control the compression ratio using COMPRESSION_ALGORITHM parameter. HIGH compression ratio will use more CPU and also increase export job time.

 

COMPRESSION_ALGORITHM
Specify the compression algorithm that should be used.
Valid keyword values are: [BASIC], LOW, MEDIUM and HIGH.

 

PARFILE WILL LOOK AS BELOW:

 cat exp_compress.par

dumpfile=dbaclass_compression.dmp
logfile=dbaclass.log
directory=EXPDIR
tables=DBACLASS.OBJECT_LIST
compression=ALL
COMPRESSION_ALGORITHM=HIGH