Monday 22 April 2013

EXPDP with oracle 10g & oracle 11g with Advanced Compression

Datapump in 11g has a good feature to reduce size of exports and resources used on machines and tapes by compressing the dumps as and when the export happens..
In version 10g, datapump did not have the compress option for data only the default option to compress METADATA only.
Example in 10g -
TEST schema using datapump -

nohup expdp \'/ as sysdba\' schemas=ACE directory=CRAMERDATA_EXP
DUMPFILE=ACESCHEMABK_22-apr13.dmp LOGFILE=ACESCHEMABK_22-apr13.log &

Export took   50 minutes.
Export dump size was 20 gb.
The same data was exported in 11g -
Only difference was adding the following bit to the expdp command in 11g  > compression=all

nohup expdp \'/ as sysdba\' schemas=ACE directory=CRAMERDATA_EXP
DUMPFILE=ACESCHEMABK_22-apr13.dmp LOGFILE=ACESCHEMABK_22-apr13.log compression=all &

Export took  15 minutes.
Export dump size 6 gb


In 11g it took just over half the time and saved 7 times the space which will tremendously reduce the amount of data moved off to tape on a daily basis especially in some databases where exports are taken daily and moved to tape.
Also there is no need to use unix pipes or uncompress dumps before commencing the import as Oracle takes care of that.

The compression clause can be specified at the tablespace, table or partition level with the following options:
NOCOMPRESS - The table or partition is not compressed. This is the default action when no compression clause is specified.
COMPRESS - This option is considered suitable for data warehouse systems. Compression is enabled on the table or partition during direct-path inserts only.
COMPRESS FOR DIRECT_LOAD OPERATIONS - This option has the same affect as the simple COMPRESS keyword.
COMPRESS FOR ALL OPERATIONS - This option is considered suitable for OLTP systems. As the name implies, this option enables compression for all operations, including regular DML statements. This option requires the COMPATIBLE initialization parameter to be set to 11.1.0 or higher. In 11gR2 this option has been renamed to COMPRESS FOR OLTP and the original name has been deprecated.

 

No comments:

Post a Comment