Monday , September 25 2017
Home / Oracle DBA / ORACLE DATAPUMP

ORACLE DATAPUMP

ORACLE DATAPUMP

DATA PUMP

  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.

COMMANDS

# 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
—————————— ——————————
DIRECTORY_PATH
SYS                            DPBKP
/u01/expbkp
# 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=’source.com’
Read More:
Oracle Database Links
Oracle Distributed Database Management
ORACLE PASSWORD FILE (PFILE)

Comments

comments

Check Also

How to switch on primary database to physical standby database

After configuration data guard then data is switching  into primary database  to standby database : …

Leave a Reply

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