Tuesday 19 February 2013

Datapump - Space Reduction

With the introduction of 11g feature with expdp/expdp compression parameter , we all are using it and greatly helps in reducing our dump size.
But do we know this is even better then gzip and produce half size then gzip. here are details:

The parameter has three options:
METDATA_ONLY - only the metadata is compressed; the data is left as it is (available in Oracle Database 10.2 as well).
... DATA_ONLY - only the data is compressed; the metadata is left alone.
ALL - both the metadata and data are compressed.
NONE - this is the default; no compression is performed.
Here is how you compress the export of the table UNITS_FACT:
$ expdp global/global directory=dump_dir tables=units_fact dumpfile=units_fact_comp.dmp compression=all

For comparison purposes, export without compression:
$ expdp global/global directory=dump_dir tables=units_fact dumpfile=units_fact_uncomp.dmp

Now if you check the files created:
$ ls -l
-rw-r----- 1 oracle dba 2576384 Jul 6 22:39 units_fact_comp.dmp
-rw-r----- 1 oracle dba 15728640 Jul 6 22:36 units_fact_uncomp.dmp

The compression ratio is 100*(15728640-2576384)/15728640 or about 83.61%! That's fairly impressive; the uncompressed dumpfile is 15MB while the compressed one is 1.5MB.
If you compress the dumpfile using gzip:
$ gzip units_factl_uncomp.dmp

-rw-r----- 1 oracle dba 3337043 Jul 6 22:36 units_fact_uncomp.dmp.gz

The compressed file is about 3.2MB, double the size of the compressed file in Data Pump. So, in addition to the compression being potentially more efficient, the decompression really adds value. When importing the dumpfile, the import does not have to decompress the file first; it decompresses as it reads it, making the process really fast.

No comments:

Post a Comment