Friday, July 10, 2009

Compress dumpfile while data pump export

With original export (exp) we can directly compress the dump file while exporting objects on unix machine by using named pipes (by mknod command). On windows machine for exp there is no such command. So after doing export operation use any compression utility in order to compress the dumpfile as a separate process.

With expdp (up to Oracle version 10.2) there is no such mechanism to compress the dumpfile directly. The COMPRESSION=METADATA_ONLY available on 10g merely compressed metadata being written to the dump file in compressed format. But for data compression directly there was no utility. Manually you would use any compression tool like zip, gzip, tar, compress or other after doing data pump export operation.

With the release of 11g the COMPRESSION parameter is enhanced and now you no need to use any compression utility. You can choose to compress the dumpfile directly while exporting.
The COMPRESSION parameter now supports following values.

COMPRESSION=ALL: It enables compression for the entire export operation.

COMPRESSION=DATA_ONLY: It results in all data being written to the dump file in compressed format.

COMPRESSION=METADATA_ONLY: It results in all metadata being written to the dump file in
compressed format. This is the default.

COMPRESSION=NONE: It disables compression for the entire export operation.

With adding COMPRESSION=ALL, while data pump export operation the dump file can now be reduced by 4/5 times or more than without COMPRESSION.

Use it as below to take a dump of all objects under schema arju and the dumpfile to be written in the directory location DATA_PUMP_DIR as compressed dump,

expdp userid=user/pass compression=all

Note that the COMPATIBLE initialization parameter should be set to "11.0.0" or higher to use these options, except for the METADATA_ONLY option.

No comments: