Import / Export using Data Pump

Up until the past 7 months all my experience has been some with 8i but mostly with 9i. I used the exp and imp utilities a great deal of the time. It made it very easy to more databases or just use the export as a form of backing up the database before modifying.

In 10g the import and export utility was upgraded to data pump. A must faster more streamlined upgrade with added features.

One of the routine chores I do is move our development database to Test and UAT (user acceptance testing). As a result I have crated several of my own utilities. A small .cmd or .bat file that calls a .par file.

The exp_database_name.cmd file. I usually name mine for the database I am working with. There is only one line of code in this file.
Simply call the EXPDP, short for export datapump. The username/password and database we are connecting to and the name of the parameter or par file. Pretty straight forward. I also name my .par file as I do the utility for the database I am working with. That way things are nice and neat and easy to understand.

EXPDP database_user/password@database_name PARFILE=exp_datbase_name.par

Now the .par file contents. There are multiple parameters that can be used. You can find additional information on datapump parameters here. The DIRECTORY is either the location of the default directory for the datapump utility created during installation of oracle. This will be something similar to this. D:\ora_app\oracle\admin\database_name\dpdump\
You can optionally create your own directory and grant a user access to that directory.
(see this post for more details – http://www.poriver.com/blog/index.cfm/2009/3/11/ORA39087-directory-name-DATAPUMPDIR-is-invalid)

DUMPFILE is the name that will be used when the export is executed. This can be anything you want. Again I usually name it to the database I am working and I put the date in the name of the file as well.

LOGFILE is the name of the log file that will be created with all the information on the database export.

SCHEMAS is the name of the schema to export. Other options are Table, Schema, Database (additional info
http://www.oracle.com/technology/oramag/oracle/04-mar/o24tech_datapump.html)

TABLE EXISTS ACTION this simple replaces the table if it exists.

DIRECTORY=DATAPUMPDIR
DUMPFILE=database_name_EXP_03062009.DMP
LOGFILE=database_name.LOG
SCHEMAS=name_of_schema
TABLE_EXISTS_ACTION=REPLACE

This is a nice way to create small utilities of your own based on the cool new datapump features.
Hope this helps.
Let me know if you have questions.

Advertisements

One comment

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s