1) Using Tables clause in expdp
If table name in upper case and db is case sensitive.
expdp tccindife/tccindife@barc11r2 directory=DATA_DIR dumpfile=abcd.dmp logfile=tccindife_tables.log content=data_only tables='TCCINDIFE.ACTB_ACCBAL_MISMATCH','TCCINDIFE.ACTB_CONSOL_STMT_FILE'
2) Example Using Parameter Files
expdp system/manager parfile=exp.par
Parameter file:exp.par
DIRECTORY = my_dir
DUMPFILE = exp_tab.dmp
LOGFILE = exp_tab.log
SCHEMAS = scott
INCLUDE = TABLE:"IN ('EMP', 'DEPT')"
3) Examples using Parameter files for all expdp and impdp
exp.sh (Contents Following): ##Initialization of the Oracle Path PATH=$PATH:$HOME/bin export ORACLE_HOME=/oraint2/ora1122/product/11.2.0/dbhome_1 export ORACLE_SID=POC1 PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin export PATH LD_LIBRARY_PATH=/oraint2/ora1122/product/11.2.0/dbhome_1/lib:$LD_LIBRARY_PATH export LD_LIBRARY_PATH #Execution of the expdp command with parfile expdp parfile=/oraint1/ora10gR2/tcdump/export.par export.par (Contents Following as a parameter file): USERID=user_name/password@Instance_name schemas=tcbnk12 directory=tcbnkdumpdir compression=all dumpfile=tcbnk12.dmp logfile=tcbnk113_21feb2012_FULL.log imp.sh (Contents Following): ##Initialization of the Oracle Path PATH=$PATH:$HOME/bin export ORACLE_HOME=/scratch/db/db1123/product/11.2.0/dbhome_1 export ORACLE_SID=jpub2wek PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin export PATH LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH export LD_LIBRARY_PATH #Execution of the impdp command with parfile impdp parfile=/scratch/work_area/DEV/dump/impdp.par impdp.par (Contents Following as a parameter file): USERID=user_name/password@Instance_name directory= tcbnkdumpdir dumpfile= tcbnk12.dmp logfile= tcbnk12.log REMAP_SCHEMA=old_schema_name:new_schema_name REMAP_TABLESPACE=old_tablespace_name:new_tablespace_name TRANSFORM=SEGMENT_ATTRIBUTES:n TRANSFORM=OID:n JOB_NAME= tcbnk12_job
4) Exclude/Include Clause with parameter file.
Impdp.par
userid=TCBNKAT121/TCBNKAT121@TCDEMO DIRECTORY=DUMP_DIR DUMPFILE=TCBNK_12.0.0_ITR2_FINAL.DMP LOGFILE=TCBNK_12.0.0_ITR2_FINAL1.log REMAP_SCHEMA=TCBNK12R2:TCBNKAT121 REMAP_TABLESPACE=TCBNK12R2:TCBNKAT121 TRANSFORM=SEGMENT_ATTRIBUTES:n TRANSFORM=OID:n COMPILE=N EXCLUDE=SEQUENCE,PROCEDURE,PACKAGE,TRIGGER,INDEX,SYNONYM,VIEW,FUNCTION,TYPE,LIBRARY IGNORE=Y GRANTS=Y INDEXES=Y ROWS=Y CONSTRAINTS=Y % expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott INCLUDE=TABLE:\"IN \(\'EMP\', \'DEP\'\)\"
5) Example with Remap_Schema
impdp warbadev21/warbadev21@me11g22 directory=datadump dumpfile=WARBA.dmp remap_tablespace=WBTCSIM1:WARBADEV21 remap_schema=WBTCSIM1:WARBADEV21 logfile=warba_dev3.log impdp USERID=TCS1131DEV/TCS1131DEV@server1 SCHEMAS=tcs1131dev DIRECTORY=DUMP DUMPFILE=expdp_tcs1131dev_full.dmp INCLUDE = PACKAGE_BODY LOGFILE=impdp_tcs1131dev_package_110120121.LOG
IMPDP using db link
impdp system/welcome1@server1 DIRECTORY=RESTORE remap_schema=PAND2BCOLL:PAND2BCOLL remap_tablespace=PAND2BCOLL:PAND2BCOLL NETWORK_LINK=DBLINK_pandgp56 logfile=imp_iut01_pandgp57_1.log
6) Example of Exporting a Full Database
To Export Full Database or Bigger size database or need to do parallel export and import, give the following command
$expdp scott/tiger DIRECTORY=data_pump_dir FULL=y DUMPFILE=partsize%U.dmp FILESIZE=10G LOGFILE=mypartexp.log JOB_NAME=mypartJob
We get multiple dump files named as partsize01.dmp, partsize02.dmp and so on. The FILESIZE parameter specifies max size of the pieces of the dump files.
7) Table level export
$expdp emp/emp DIRECTORY=dpump_dir_tab DUMPFILE=selectedtables.dmp TABLES=employees,jobs,departments
8) Exporting selective tablespaces
$expdp emp/emp DIRECTORY=dpump_dir_ts DUMPFILE=selected_tbs.dmp TABLESPACES=TBS_emp,TBS_dpt,TBS_Mgr
This will export all the objects from TBS_emp,TBS_dpt,TBS_Mgr
9) Excluding and Including Objects in expdp
$expdp scott/tigers DIRECTORY=data_pump_dir DUMPFILE=exclude.dmp SCHEMAS=SCOTT EXCLUDE=TABLE:"like 'C%'"
Here all tables in Scott’s Schema whose name starts with “C “ will not be exported.
$expdp scott/tiger DIRECTORY=data_pump_dir DUMPFILE=scott_schema.dmp SCHEMAS=SCOTT INCLUDE=TABLE:"like 'Z%'"
Here all tables in Scott’s Schema whose name starts with “Z “ will be exported.
Similarly you can also exclude INDEXES, CONSTRAINTS, GRANTS, USER, SCHEMA
10) Adding Query to Filter out Rows during Export
expdp emp/passw QUERY=emp:'"WHERE emp_id > 10 AND sal > 90000"' NOLOGFILE=y DIRECTORY=dpump_test DUMPFILE=query.dmp
11) Expdp/Impdp in different directories/different mountpoints in same server
expdp dumpfile=DATA_PUMP_DIR:TESTONE_datapumpdir_%U.dmp,EXPBKP2:TESTONE_expbkp2_%U.dmp logfile=DATA_PUMP_DIR:TESTONE_expdp.log full=y compression=all parallel=2
impdp dumpfile=DATA_PUMP_DIR:TESTONE_expdp_%U.dmp,EXPBKP2:TESTONE_expdp_%U.dmp logfile=TESTONE_impdp.log directory=DATA_PUMP_DIR parallel=2 schemas=SCOTT
12) Points for parallel EXPDP/IMPDP
As a general rule for processes you find the cpu_count parameter (v$parameter) and use cpu-count-1. However. the Oracle Utilities documentation makes a slightly different recommendation for using a Data Pump export
select name,value from v$parameter where name = ‘cpu_count’;
1) Set the degree of parallelism to two times the number of CPUs, then tune from there.
2) For Data Pump Export, the PARALLEL parameter value should be less than or equal to the number of output dump files.
For Data Pump Import, the PARALLEL parameter value should not be much larger than the number of files in the dump file set.
EXAMPLE 1)
export.par FILESIZE=10G compression=all parallel=32 logfile=expdp_t7.log dumpfile=expd_t7_%u.dmp schemas=fcubs1201 directory=expdir COMPRESSION=ALL JOB_NAME=fcubs1201_job
EXECUTE
expdp parfile=export.par Username: sys as sysdba Password:welcome1
EXAMPLE 2)
expdp vallep/password directory=datapump full=y dumpfile=data.dmp filesize=2G parallel=2 logfile=full.log
EXAMPLE 3):
impdp system/sysdec2012@TRAININGDB schemas=test parallel=2 DIRECTORY=DATA_DUMP DUMPFILE=test_%U.dmp LOGFILE=test.log JOB_NAME=testJob content=metadata_only
13) REUSE_DUMPFILE : Overwrite existing dumpfile)
This is the option with data pump expdp utility. Normally when you perform the export using expdp utility and if the dumpfile is present in the export directory it will throw an error “ORA-27038: created file already exists”. This situation happens when you wanted to perform the repetitive exports using the same dumpfile. Oracle provides an option reuse_dumpfile=[Y/N] to avoid this error. You should mention the parameter value as Y to overwrite the existing dump file. By default the option considered as N.
$ expdp scott/tiger directory=exp_dir dumpfile = x.dmp table s= example reuse_dumpfiles = y14)Verification of status of IMPDP job
