Oracle Data Pump

The Data Pump utility was introduced from Oracle 10g R1 onwards. This boasts very high data transfer between databases. The Data Pump provides two utilities as mentioned below:

• Data Pump Export : expdp
• Data Pump Import : impdp

They might seem similar to the traditional exp and imp commands but they actually aren't! The export files from expdp cant be imported using the traditional import command imp. Also export files from exp cant be imported using impdp. The utilities are server related rather than client related with regard to the traditional ones.

Okay, enough of theories! Let's look at some examples:

Step 1: Log into the database as SYS user:

splus sys as sysdba

Step 2: Creating directory for dumping and setting permissions

create directory DUMPDIR as '/home/oracle/dumpdir';

Grant necessary permissions for the user who needs the export their schema:

grant read,write on directory dumpdir to user1;

Step 3: Exporting the schema

expdp user1/passwd DIRECTORY=DUMPDIR DUMPFILE=user1.expdp.dmp SCHEMAS=user1;

Step 4: Importing the schema

impdp user1/password DIRECTORY=DUMPDIR DUMPFILE=user1.expdp.dmp SCHEMAS=user1;