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;

Oracle Export and Import - The Traditional Way!

Even though its possible to pass the requires parameters during exp or imp command run time, its convenient to use single-line full commands when its to be used in an automated export/import scripts:

Complete Database Export:

To export the complete Oracle Database and import it to another machine, it requires SYSTEM user privileges. Here is how its done:

exp SYSTEM/password FULL=y FILE=completedb.dmp LOG=dba.log CONSISTENT=y

 Here:

Full=y : denotes a complete database export is required.
FILE=filename.dmp : denotes the file name of the export file.
LOG=logname.log : denotes the log file to be written.
CONSISTENT=y : denotes no further changes shall be affected in the export file.

To import the complete database in another machine, here is how to do it:

imp SYSTEM/password FULL=y FIlE=completedb.dmp commit=y

User Schema Export:

An user can export his/her own schema using the below command:

exp username/password FILE=userdb.dmp OWNER=username

To import the schema, the below command shall be used:

imp SYSTEM/password FROMUSER=username1 TOUSER=username2 FILE=userdb.dmp

Here "username1" is the name of the user who exported the schema and "username2" is the name of the user to which the schema needs to be imported.

User Schema Export (Table specific):

To export a schema with only selected tables: 

exp username/password FILE=userdb-tables.dmp TABLES=(table1,table2)

To import that export file back to another user, the below command shall be used:

imp SYSTEM/password FROMUSER=username1 TOUSER=username2 FILE=userdb-tables.dmp TABLES=(table1,table2)