<<Back to Oracle DATAPUMP Main Page
Compressed database export using expdp
Use COMPRESSION parameter of expdp to perform compressed exportDefault: METADATA_ONLY
Syntax and DescriptionCOMPRESSION=[ALL | DATA_ONLY | METADATA_ONLY | NONE]
ALL enables compression for the entire export operation.
DATA_ONLY only data being written to the dump file is compressed.
Note: Both The ALL amd DATA_ONLY options requires that the Oracle Advanced Compression option be enabled.
While taking compressed export you can even define the algorithm to be used for compression using COMPRESSION_ALGORITHM parameter if not explicitly defined default: BASIC is used
Default: BASIC
Syntax and DescriptionCOMPRESSION_ALGORITHM = {BASIC | LOW | MEDIUM | HIGH}
Note: to use COMPRESSION_ALGORITHM database compatibility should be 12.0.0 or higher and Oracle Advanced Compression option should be enabled.
Example
$ cat exp_full_pdb01.par
directory=dump
dumpfile=EXP_PDB01_FULL%U.dmp
logfile=EXP_PDB01_FULL.log
full=y
COMPRESSION=ALL
directory=dump
dumpfile=EXP_PDB01_FULL%U.dmp
logfile=EXP_PDB01_FULL.log
full=y
COMPRESSION=ALL
$ expdp system@PDB01 parfile=exp_full_pdb01.par
Export: Release 12.2.0.1.0 - Production on Thu Oct 11 12:05:13 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Password:
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_FULL_02": system/********@PDB01 parfile=exp_full_pdb01.par
............................................................................................................................................
............................................................................................................................................
How to Check if the Compression is used and what algorithm
SQL> select utlname, COMPRESSCNT, COMPRESSBAS, COMPRESSLOW, COMPRESSMED, COMPRESSHGH from ku_utluse where utlname ='Oracle Utility Datapump (Export)';
Of course you can check the size of dumpfile to determine the amount of space you saved using compression
$ du -sk EXP_PDB01_FULL01.dmp
868 EXP_PDB01_FULL01.dmp
868 EXP_PDB01_FULL01.dmp
Let us change the parameter file for expdp to use the compression_algorithm HIGH
$ cat exp_full_pdb01.par
directory=dump
dumpfile=EXP_PDB01_FULL%U.dmp
logfile=EXP_PDB01_FULL.log
full=y
COMPRESSION=ALL
directory=dump
dumpfile=EXP_PDB01_FULL%U.dmp
logfile=EXP_PDB01_FULL.log
full=y
COMPRESSION=ALL
COMPRESSION_ALGORITHM=HIGH
$ expdp system@PDB01 parfile=exp_full_pdb01.par
Export: Release 12.2.0.1.0 - Production on Thu Oct 11 13:43:39 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Password:
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_FULL_02": system/********@PDB01 parfile=exp_full_pdb01.par
Starting "SYSTEM"."SYS_EXPORT_FULL_02": system/********@PDB01 parfile=exp_full_pdb01.par
............................................................................................................................................
............................................................................................................................................
$ du -sk EXP_PDB01_FULL01.dmp
556 EXP_PDB01_FULL01.dmp
556 EXP_PDB01_FULL01.dmp
NOTE:- If you use compression yes will save space on the disk to store the dump file however keep in mind that compression is a recourse intensive operation and therefore you will have to compromise with performance overhead
Comments
Post a Comment