Compression in EXPDP datapump jobs in Oracle

Compression in EXPDP datapump jobs in Oracle

Compression is used to reduce the data dump file size generated by datapump jobs while writing data to dump file.
It will help to move the larger dump file easily through network by redusing its size.

Note:
1. Feature is used with Enterprise edition of Oracle.
2. Database compatibility parameter should be 11.0.0 or higher

Compression has following option

COMPRESSION=[ALL | DATA_ONLY | METADATA_ONLY | NONE]

ALL: Enable compression for all data and metadata while generating dump file.
DATA_ONLY: Only data written to dump file is in compressed.
METADATA_ONLY: Writing metadata to dump file is in compressed format.(DEFAULT)
NONE: Disable compression

Example of EXPDP with Compression

expdp hr@xepdb1 directory=dbbackup dumpfile=dump.dmp logfile=dump.log compression=ALL

For 12 version, you can define the COMPRESSION_ALGORITHM
Define the compression algorithm to be used when compressing dump file data. How much compression needed as per your need you can define with it.
Note: Compatibility should be 12 or higher.

Options
COMPRESSION_ALGORITHM = {BASIC | LOW | MEDIUM | HIGH}

BASIC: good combination of compression ratios and speed
LOW: Its compression use easy algorithm with less impact on CPU of system and with less compression ratio.
MEDIUM: like basic combination of both rations and speed.
HIGH: High compression ration to reduce size, its suitable for network transfer.

Example

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp COMPRESSION=ALL COMPRESSION_ALGORITHM=BASIC

If you used in express edition the following error occurred during EXPDP:

C:\Users\oracle>expdp hr@xepdb1 directory=dbbackup compression=ALL
Export: Release 18.0.0.0.0 - Production on Wed Apr 8 17:44:00 2020
Version 18.4.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
ORA-39002: invalid operation
ORA-00439: feature not enabled: Dump File Data Compression

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.