DBAs are plumbers

Data Pump is my favorite feature in Oracle 10g.

Described as Export/Import on steriods, Data Pump is faster and much more manageable way to transfer data between Oracle systems and if you are not using it you don’t know what you are missing.

Many smart people have already written quite a bit about Data Pump and it is even part of the official Oracle 10g Top 20 features.

I found Data Pump beneficial in three scenarios:

  1. We take a full export of each data base once a day. I’m aware that export is not recommended as a method to backup the database, however having a full export around already saved our behinds more than once, so I make sure we have one. Just in case.
    The daily export used to take 16 hours on a large database during which the CPU usage and IO waits on the system were too high for comfort. With Data Pump the daily export takes just 3 hours, which can be scheduled during off-business hours with minimal impact.
  2. Large part of my routine daily work is to copy schemas from development to testing to production and vice versa. Since we have different tablespaces and different naming conventions on those environments, using export and import for the copy used to be a nightmare.
    We had to create the user in the new system, import the schema into SQL script, replace the schema name and tablespace name in the script, run it to create the schema and only then import the data. The process took around an hour.
    Now with data pump its a 5 minute job. You simply tell the import command to translate the schema name and tablespace name and it will even create the user in the new system for you, with all the correct permissions.
  3. We also use Data Pump to create new schema. One of our applications demand that each customer will have its own schema, but all of these schema are identical (same tables, views, permissions, synonyms, etc). To create a new schema we simply use Data Pump to import an empty schema that we exported in the past with a new user name. This is not a huge improvement over running a script for creating new users, but it’s still pretty neat.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s