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
