DATA PUMP using expdp and impdp utility

 Summary

  1.Pre-requiste

  2.Table Level Export & Import

  3.Schema Level Export & Import

  4.Tablespace Level Export & Import

  5.Conditinal Export & Import

  6.Full Database Export & Import

  7.Export & Import using Par file

1.Pre-requiste:

  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 directory at OS level

  

  # mkdir -p /u02/dp_exp_dir

  # chown -R oracle:oinstall /u02

  # chmod -R 775 /u02

    --

  $ cd /u02/dp_exp_dir

  $ ls -ltr

  

  => Create directory inside the database

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

  

  => Grant permissions on directory

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

  

  => View directory information

     SQL> select * from dba_directories;

2. Table Level Export & Import:

 

  $ expdp directory=<name> dumpfile=<emp_bkp_<14mar23>.dmp logfile=emp_bkp_<14mar23>.log tables='SCOTT.EMP' jobname=expemp1

    --

  $ nohup expdp directory=<name> dumpfile=<emp_bkp_<14mar23>.dmp logfile=emp_bkp_<14mar23>.log tables='SCOTT.EMP' & jobname=impemp1

  => Import table where source and target schema are same

  

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

  

  => Import table to another schema

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

  

  => Import tables into another tablespace (only in datapump)

  

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

  

  => Import table to a different name or rename table or remap_table

  

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

  

  => Import only the rows from an exported table without loading table any table definitions

  

  $ impdp directory=<datapump> dumpfile=emp_bkp.dmp logfile=imp_emp.log tables='EMP' content=DATA_ONLY

3. Schema Level Export & Import:

  

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

    --

  $ expdp "sys/<password> as sysdba" directory=<datapump> dumpfile=scott_bkp.dmp logfile=scott_bkp.log schemas='SCOTT'

  

  Import source schema objects into same schema on target

  

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

  

  => Import source schema objects into a different schema on target

  

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

4.Conditional Export & Import:

  

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

  

  => Import rows where source and target schema are same

  

  $ impdp directory=datapump dumpfile=emprows_bkp.dmp logfile=imp_emprows.log tables='SCOTT.EMP'

5.Full Database Export & Import:

 

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

  

  => Import full database 

  

  SQL> select name from v$tablespace; -> On source & On target

  

  1.Create missing tablespaces on target

  2.Make sure target tablespace has enough free space

  3.Drop all non-oracle schemas (done during refresh)

  4.DROP USER <username> CASCADE;

  

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

6. Export & Import using Par file:

  

  expdp 

  directory=datapump 

  dumpfile=emprows_bkp.dmp 

  logfile=emprows_bkp.log 

  tables='SCOTT.EMP' 

  query=\"where deptno=10\"