Oracle Data Pump Utility – expdp, impdp

Oracle Data Pump is a fast data movement utility provided by Oracle. It’s an upgrade to old EXP and IMP utility. The Data Pump utility has been built from scratch and it has a completely different architecture.

Watch Oracle Data Pump architecture video

The first step in Oracle Data Pump is to create an OS level directory which will be used by Oracle for performing exports and imports.

Create Data Pump Directory

Create directory at OS level

# mkdir -p /u02/dp_exp_dir

Create directory inside the database

SQL> create directory datapump as '/u02/dp_exp_dir';

Grant permissions on directory

SQL> grant read,write on directory datapump to scott; 

View directory information

SQL> select * from dba_directories;

Perform Data Pump Export

In case you want to know all the options available with expdp utility, you can use below command

$ expdp help=y

To take database level export

$ expdp directory=datapump dumpfile=fullprod.dmp logfile=fullprod.log full=y 

To take schema level export

$ expdp directory=datapump dumpfile=scott_bkp.dmp logfile=scott_bkp.log schemas='SCOTT'

To take table level export

$ expdp directory=datapump dumpfile=emp_bkp.dmp logfile=emp_bkp.log tables='SCOTT.EMP'

To take row level export

$ expdp directory=datapump dumpfile=emprows_bkp.dmp logfile=emprows_bkp.log tables='SCOTT.EMP' query=\"where deptno=10\"

Performing Data Pump Import

In case you want to know all the options available with impdp utility, you can use below command

$ impdp help=y

To import full database

$ impdp directory=datapump dumpfile=fullprod.dmp logfile=imp_fullprod.log full=y

To import source schema objects into same schema name

$ impdp directory=datapump dumpfile=scott_bkp.dmp logfile=imp_schema.log remap_schema='SCOTT:SCOTT'

To import source schema objects into a different schema

$ impdp directory=datapump dumpfile=scott_bkp.dmp logfile=imp_schema.log remap_schema='SCOTT:IMP_TEST'

To import a table

$ impdp directory=datapump dumpfile=emp_bkp.dmp logfile=imp_emp.log tables='EMP' remap_schema='SCOTT:SCOTT'

To import a table to another user

$ impdp directory=datapump dumpfile=emp_bkp.dmp logfile=imp_emp.log tables='EMP' remap_schema='SCOTT:SYSTEM'

To import tables to another tablespace (only in datapump)

$ impdp directory=datapump dumpfile=emp_bkp.dmp logfile=imp_emp.log tables='EMP' remap_schema='SCOTT:SCOTT' remap_tablespace='MYDATA:MYTBS'

Was this article helpful?

Related Articles

Leave a Comment