Data Pump Gets Better

Today I will Discuss some new Features of Datapump in 11G

One of the big issues with Data Pump was that the dumpfile couldn't be compressed while getting created. That was something easily done in the older Export/Import utility. In Oracle Database 11g, Data Pump can compress the dumpfiles while creating them. This is done via a parameter COMPRESSION in the expdp command line. 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

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.

The two other enhancements in Data Pump are:

Encryption: the dumpfile can be encrypted while getting created. The encryption uses the same technology as TDE (Transparent Data Encryption) and uses the wallet to store the master key. This encryption occurs on the entire dumpfile, not just on the encrypted columns as it was in the case of Oracle Database 10g.

Masking: when you import data from production to QA, you may want to make sure sensitive data such as social security number, etc. are obfuscated (altered in such a way that they are not identifiable). Data Pump in Oracle Database 11g enables you do that by creating a masking function and then using that during import.