Himu\’s Attempt at Blogging

Tidbits from my thoughts

Using Oracle 10g Data Pump Export

with 4 comments

I recently installed Oracle 10g Release 2 ( on a Dell PowerEdge 2800 (Dual Xeon 2.8 GHz, 2GB RAM and 2 x 73GB SCSI HDD) running Red Hat Enterprise Linux 4 for self-tutoring administration.For better understanding, I didn’t create a database during installation (instance-only setup). The database was created separately following instructions from the Apress book Expert Oracle 10g Administration (ISBN 1-59059-451-7).

OS Environment variables:


initnwndb.ora directory entries:

Datafile and redo log location: /oradata/nwndb

After importing a dump of our application’s schema from a production 9i server, I wanted to do a dump (for local mediocre snapshot backup) using 10g’s new technology – the Oracle Data Pump. Export and import using this new technology is faster and flexible. For details about the data pump features, you can consult the ‘Oracle Database Utilities’ manual and also the Apress book.

Oracle Data Pump has three parts:

  1. The expdp and impdp command-line client programs
  2. The Data Pump API
  3. The Metadata API

During execution of expdp or impdp, you need to specify a dump directory. The directory should exist on the server and must be identified using a directory object you have privilege to write from and/or read to (expdp needs write only whereas impdp needs both). Oracle has a default directory object DATA_PUMP_DIR defined for privileged users. I found my installation’s default value as:

SQL> select * from all_directories;
OWNER                          DIRECTORY_NAME
------------------------------ ------------------------------
SYS                            DATA_PUMP_DIR

I didn’t like the location and so changed it to my liking by first logging out of SQL*Plus, creating the physical directory and issuing the appropriate commands from SQL*Plus again:

$ mkdir dpdump
$ mkdir dpdump/nwndb
$ sqlplus system/********
SQL> create or replace directory DATA_PUMP_DIR as '/u01/app/oracle/dpdump/nwndb';
Directory created.
SQL> grant read, write on directory DATA_PUMP_DIR to eibsfex;
Grant succeeded.
SQL> quit;

I cannot use the Data Pump out-of-the-box as I created the database manually. I needed to create the supporting PL/SQL packages by executing dbmspump.sql and dbmsmeta.sql as sysdba from $ORACLE_HOME/rdbms/admin directory:

SQL> conn sys/****** as sysdba;
SQL> @$ORACLE_HOME/rdbms/admin/dbmspump.sql
SQL> @$ORACLE_HOME/rdbms/admin/dbmsmeta.sql

Also, I needed to grant the create table privilege to the application schema user (‘eibsfex’ in this case) with which I want to execute the export/import:

SQL> grant create table to eibsfex;

Now I’m ready to execute a Data Pump Export. There are four export modes – full, schema, tablespace, and table. I’ll use the schema mode of export as I want the complete application schema to be dumped. The expdp command is:

$ expdp eibsfex directory=DATA_PUMP_DIR parallel=2 dumpfile=eibsfex_%U.dmp logfile=eibsfex_dmp.log schemas=eibsfex job_name=eibsfex.export_job


eibsfex is the Oracle user name under which the job is run

directory=DATA_PUMP_DIR says I want to use the already defined directory location

parallel=2 means I want to parallel threads of execution hoping better performance (maximum allowed is 99)

dumpfile=eibsfex_%U.dmp specifies the dump file set; each of the two threads will work on a separate file replacing %U with the thread number (allowable range is 01 to 99)

logfile=eibsfex_dmp.log tells the job to use eibsfex_dmp.log to write logs to

schemas=eibsfex should be obvious

I hope to write about the Data Pump Import after I do it.


Written by mhimu

August 11, 2007 at 1:18 pm

Posted in Oracle

4 Responses

Subscribe to comments with RSS.

  1. ei gula ki habijabi…. tui ekhono programming charte parli na !!!!


    August 21, 2007 at 10:21 am

  2. arif o programming korte pare kina jani na kintu tomar spelling e mistake acche na korte hobe.


    September 11, 2007 at 12:18 pm

  3. charte na korte


    September 11, 2007 at 12:19 pm

  4. ami to ekta jinish bujhlam na 😕 — eikhane programming-ta kothai? :-/


    September 12, 2007 at 10:13 am

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

%d bloggers like this: