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=exatree_wo_compression.dmp
logfile=exatree.log
directory=EXPDIR
tables=EXATREE.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 "EXATREE"."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/exatree__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=exatree_compression.dmp
logfile=exatree.log
directory=EXPDIR
tables=EXATREE.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 "EXATREE"."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/exatree_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 COMPRESSIONWITH COMPRESSIONSPACE REQUIRED25mb3 mbTIME TAKEN3 second5 secondCPU USAGENORMALbit more than w/o compressionLICENSE?No additional licenseAdvance 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=exatree_compression.dmp
logfile=exatree.log
directory=EXPDIR
tables=EXATREE.OBJECT_LIST
compression=ALL
COMPRESSION_ALGORITHM=HIGH
compressionEXPDP BACKUP & RECOVERY