• Arun Kumar

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'


594 views

Recent Posts

See All

Oracle 12c to 19c Database Upgrade

In this article we will be looking at two most commonly used methods of upgrading Oracle database from 12c to 19c Pre-upgrade tasks Upgrade using DBUA Upgrade using Manual Method You can perform a dir