Friday , October 20 2017




  1. Datapump is an extension to traditional exp/imp which provides more advantages like security, fastness etc.
  2. During  datapump export, oracle will create master table in the corresponding schema and data will be transferred parallely from tables to dumpfile.
  3. During datapump import this will happen in reverse order i.e from dumpfile a master table will be created and from that original tables.
  4. After finishing either export or import in datapump, oracle will automatically drops master table.
  5. Just like exp/imp, datapump also contains 4 (database, schema, table and row) levels.
  6. In datapumpdumpfile will reside only on server and cannot be created on client side with the help of directory option. This will provide security to dumpfile.
  7. DBA_DATAPUMP_JOBS view can be used to find the status of datapump export or import process.
Note: whenever datapump export is done using PARALLEL option, import also should be done with the same option. Otherwise it will effect the time taking for import.
  1. Oracle will try to import tables to the tablespace with same name and if tablespace doesn’t exist, it will go to users default tablespace.


# To create a directory
SQL> create directory dpbkp as ‘/u01/expbkp’;
Directory created.
# To grant permissions on directory
SQL> grant read,write on directory dpbkp to scott;
Grant succeeded.
# To view directory information
SQL> select * from dba_directories;
OWNER                          DIRECTORY_NAME
—————————— ——————————
SYS                            DPBKP
# To know options of datapump export/import
[oracle@server1 ~]$expdp help=y
[oracle@server1 ~]$impdp help=y
# To take database level export
[oracle@server1 ~]$ expdp directory=dpbkpdumpfile=fullprod.dmp logfile=fullprod.log full=y
# To take schema level export
[oracle@server1 ~]$ expdp directory=dpbkpdumpfile=scott_bkp.dmp logfile=scott_bkp.log schemas=’SCOTT’
# To take table level export
[oracle@server1 ~]$ expdp directory=dpbkpdumpfile=emp_bkp.dmp logfile=emp_bkp.log tables=’SCOTT.EMP’
# To take row level export
[oracle@server1 ~]$ expdp directory=dpbkpdumpfile=emprows_bkp.dmplogfile=emprows_bkp.log tables=’SCOTT.EMP’ query=\”where deptno=10\”
# To import full database
[oracle@server1 ~]$ impdp directory=dpbkpdumpfile=fullprod.dmp logfile=imp_fullprod.log full=y
# To import a schema
[oracle@server1 ~]$ impdp directory=dpbkpdumpfile=scott_bkp.dmp logfile=imp_schema.log remap_schema=’SCOTT:SCOTT’
# To import a table
[oracle@server1 ~]$ impdp directory=dpbkpdumpfile=emp_bkp.dmp logfile=imp_emp.log tables=’EMP’ remap_schema=’SCOTT:SCOTT’
# To import a table to another user
[oracle@server1 ~]$ impdp directory=dpbkpdumpfile=emp_bkp.dmp logfile=imp_emp.log tables=’EMP’ remap_schema=’SCOTT:SYSTEM’
# To import tables to another tablespace (only in datapump)
[oracle@server1 ~]$ impdp directory=dpbkpdumpfile=emp_bkp.dmp logfile=imp_emp.log tables=’EMP’ remap_schema=’SCOTT:SCOTT’remap_tablespace=’MYDATA:MYTBS’
# To import without taking export (using network_link option)
[oracle@server1 ~]$ impdp directory=dpbkpdumpfile=emp_bkp.dmp logfile=imp_schema.log schemas=’SCOTT’ network_link=’’
Read More:
Oracle Database Links
Oracle Distributed Database Management



Check Also

Moving a database from Normal File System to ASM storage

Default storage for oracle database is File System, where database files resides on local storage. …

Leave a Reply

Your email address will not be published. Required fields are marked *