Wednesday, May 21, 2008

11g New Features: Data pump enhancements

I was going through the enhancements made to Oracle Data pump import/export utilities and a couple of them got my attention. To be very specific I liked the newly introduced remap_table and remap_data parameters most :-

  • Remap_Data
Remap the data of a column while importing; Basically you would use a function to do the remap. Particularly useful in numerous imports of production data. Now you can use this parameter to mask/convert data such as passwords, credit card details, email ids etc. Isn't that really cool?
  • Remap_Table
Now its possible to rename a table while importing. Useful? Yes, I think so; In situations where you keep table exports or want to get data from production and compare with stage data during bug fixing etc. With this parameter you would not need to import the table into a different schema (coz the table already exists in stage database) to compare. Indeed, another useful parameter.
Apart from the ones listed above, Oracle 11g has introduced a few other enhancement to data pump utilities which are :-

1. data compression => compress data and metadata before writing to the dump files
2. compress dump files => reduces dump file size by 10-15%
3. encryption features => you can encrypt data during export and can also specify encryption algorithm. you can encrypt dump files too!
4. data options => to ignore non-deferrable constraints
5. reuse dumpfiles => overwrite dump files; earlier data pump export would return an error if a dump file with same name exists. so now it will overwrite any existing dump files. its both good and bad since you may have to move dump files to a different location if you have a need to retain them.
6. transportable option for tables

In all, quite a few enhancements to data pump utility though I still don't understand the "transportable" option for tables and where/how do I put it to use. Thoughts?

No comments: