EXPDP/IMPDP Examples

EXPORT/IMPORT only Tables
-----------------------------------------------------------------------------------------------------
Contents of expdp_ora_TBL.ksh

expdp ksomaiy2@usfbBAT parfile=exp_tbl.par

Contents of exp_tbl.par

DIRECTORY=IM_EXPORTS
DUMPFILE=dbcr2013_10_25_BAT_V1.dmp
LOGFILE=dbcr2013_10_25_BAT_V1.log
SCHEMAS=REFDB
INCLUDE=TABLE:"IN ('ACCOUNT','TL_CM_PRODUCT_TYPE','TL_CM_PRODUCT_TYPE_HST','TL_CM_PRODUCT_LOAN_TYPE','TL_CM_PRODUCT_LOAN_TYPE_HST')"

Contents of impdp_ora_TBL.ksh

impdp ksomaiy2@usfbDEV parfile=imp_tbl.par

Contents of imp_tbl.par

DIRECTORY=IM_EXPORTS
DUMPFILE=dbcr2013_10_25_BAT_V1.dmp
LOGFILE=dbcr2013_10_25_BAT_imp.log
SCHEMAS=REFDB
INCLUDE=TABLE:"IN ('ACCOUNT','TL_CM_PRODUCT_TYPE','TL_CM_PRODUCT_TYPE_HST','TL_CM_PRODUCT_LOAN_TYPE','TL_CM_PRODUCT_LOAN_TYPE_HST')" TABLE_EXISTS_ACTION=REPLACE

--------------------------------------------------------------------------------------
EXPORT/IMPORT Schema (With Schema Replace)
--------------------------------------------------------------------------------------

Contents of "exp_SS_CM2.par"

expdp ksomaiy2@usfbDEV parfile=exp_SS_CM2.par


Contents of exp_SS_CM2.par

DIRECTORY=IM_EXPORTS
DUMPFILE=SS_CM2_BKP_10282013_PAT.dmp
LOGFILE=SS_CM2_BKP_10282013_PAT.log
SCHEMAS=SS_CM2_BKP
Contents of impdp_ora_DEV_TBL.ksh

impdp ksomaiy2@usfbDEV parfile=imp_DEV.par  

Contents of "imp_DEV.par"

DIRECTORY=IM_EXPORTS
DUMPFILE=SS_CM2_10282013_PAT.dmp
LOGFILE=SS_CM2_IMP_10282013.log
REMAP_SCHEMA=SS_CM2:SS_CM2_BKP
TABLE_EXISTS_ACTION=REPLACE
transform=OID:n

In addition to this while creating the types the contents you need to grant execute on all the types in SS_CM2 to SS_CM2_BKP

--------------------------------------------------------------------------------------
EXPORT/IMPORT Schema (Import Data only)
--------------------------------------------------------------------------------------
The schema and the schema objects are created using a DDL. 
Schema is recompiled after the DDl Run

 The data is only imported in the schema

Contents of impdp_ora_DEV_TBL.ksh

impdp ksomaiy2@usfbDEV parfile=imp_DEV.par 


Contents of "imp_DEV.par"


DIRECTORY=IM_EXPORTS
DUMPFILE=SS_CM2_10282013_PAT.dmp
LOGFILE=SS_CM2_IMP_10292013.log
REMAP_SCHEMA=SS_CM2:SS_CM2_BKP
CONTENT=DATA_ONLY
DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS
transform=OID:n

The Skip constraints error only skips the rows that are erroring out due to constraints.
However to load the data failing with constraints you need to drop and recreate the constraints after the load.

---------------------------------------------------------------------------------------
EXPORT/IMPORT  SQLFILE Option only
---------------------------------------------------------------------------------------

DIRECTORY=IM_EXPORTS
DUMPFILE=SS_CM2_10282013_PAT.dmp
SQLFILE=SS_CM2.sql




Generates DDL for the Schema SS_CM2