I'm Top Oracle DBA

Oracle Data Pump (expdp, impdp) in Oracle Database 10g onward

|
  • Before export or import, You have to create a directory for an object, it can access. The directory object is only a pointer to a physical directory, creating it does not actually create the physical directory on the file system of the database server.
  • A data pump is a logical Backup, You can back up only table, schemas, database, indexes, database link and anything that depend on your wants.
  • within script exp or expdp, You can add parfile, include, exclude, content and write script(Query) on your own, I think that is very useful for me.
  • You can rename tablespaces or table names or owners of objects by remap command when you import data pump.

Table expdp/impdp

  • expdp system/passwordadmin#345 directory=exp_dir tables=eeeowner.bmx_project,
    eeeowner.bmx_l_cluster,eeeowner.bmx_l_st_item,eeeowner.bmx_l_strategy,
    eeeowner.bmx_unit,eeeowner.bmx_pro_activity dumpfile=eeeowner_qas.dmp
    logfile=eeeowner_qas.out
  • impdp system/passwordadmin#345 directory=exp_dir dumpfile=eeeowner_qas.dmp logfile=imp_eeeowner_qas.out

Schema expdp/impdp

  • expdp system/”passwordadmin#345″ directory=test_dir dumpfile=slab_ecm.dmp
    logfile=slab_ecm.log schemas=slab_ecm
  • impdp system/”passwordadmin#345″ directory=test_dir dumpfile=slab_ecm.dmp logfile=imp_slab_ecm.log

DB expdp/impdp

  • expdp system/”passwordadmin#345″ directory=test_dir dumpfile=labdbt1_full.dmp full=y logfile=labdbt1_full.log
  • impdp system/”passwordadmin#345″ directory=test_dir dumpfile=labdbt1_full.dmp logfile=imp_labdbt1_full.log

Include/Exclude

Include

  • expdp system/”passwordadmin#345″ directory=test_dir dumpfile=dblinkslab_ecm.dmp
    logfile=dblinkslab_ecm.log schemas=slab_ecm include=db_link
    or
    expdp system/”passwordadmin#345″ dumpfile=test_dir:dblinkslab_ecm.dmp
    logfile=test_dir:dblinkslab_ecm.log schemas=slab_ecm include=db_link
  • expdp system/”passwordadmin#345″ directory=test_dir dumpfile=dblinkslab_ecm.dmp
    logfile=dblinkslab_ecm.log schemas=slab_ecm include=indexes
    or
    expdp system/”passwordadmin#345″ dumpfile=test_dir:dblinkslab_ecm.dmp
    logfile=test_dir:dblinkslab_ecm.log schemas=slab_ecm include=indexes

exclude

  • expdp system/”passwordadmin#345″ directory=test_dir dumpfile=dblinkslab_ecm.dmp
    logfile=dblinkslab_ecm.log schemas=slab_ecm exclude=package
    or
    expdp system/”passwordadmin#345″ dumpfile=test_dir:dblinkslab_ecm.dmp
    logfile=test_dir:dblinkslab_ecm.log schemas=slab_ecm exclude=package

Content

  • expdp system/”passwordadmin#345″ directory=test_dir dumpfile=slab_ecm.dmp
    logfile=slab_ecm.log schemas=slab_ecm content=data_only
  • expdp system/”passwordadmin#345″ directory=test_dir dumpfile=slab_ecm.dmp
    logfile=slab_ecm.log schemas=slab_ecm content=metadata_only

Query

  • expdp system/systemadmin12345 directory=exp_dir dumpfile=expdpv2.dmp
    logfile=expdpv2.log
    query=’systemad.v_report_itemre_tab01:”WHERE tno=0″,systemad.v_report_itemre_tab08:”WHERE tno=0″‘

Remap expdp/impdp

  • expdp system/systemadmin12345 directory=exp_dir dumpfile=expdpv.dmp
    logfile=expdpv.log version=10.2.0.3.0 tables=systemad.v_report_itemre_tab01,
    systemad.v_report_itemre_tab02,systemad.v_report_itemre_tab05,
    systemad.v_report_itemre_tab07,systemad.v_report_itemre_tab08,
    systemad.v_report_itemre_tab09,systemad.v_report_tab15_tcode
  • impdp system3/systemadmin345 directory=exp_dir2 dumpfile=expdpv.dmp remap_schema=systemad:itemre_owner
    remap_tablespace=users:webmap_data
***Note*** 
***.par file, You can use everything as a table, include, exclude, dump file, logfile,…******in this case, I showed 2case as a table and include***

***Example***

expdp system/passwordadmin#345 directory=exp_dir parfile=tables1.par dumpfile=eeeowner_qas.dmp
logfile=eeeowner_qas.out

In a file tables1.par (for tables)

tables=eeeowner.bmx_project,eeeowner.bmx_l_cluster,eeeowner.bmx_l_st_item,eeeowner.bmx_l_strategy,
eeeowner.bmx_unit,eeeowner.bmx_pro_activity

***you can use parfile for include, You have to identify syntax like as below.***
include=table:\”in (\’bmx_project\’, \’bmx_pro_activity\’)\”

***if you have a single file***
include=table
include=view
include=package:”like ‘%act'”

***End Note***
1,902
error: Content is protected !!