17 | 10 | 2017
Latest Articles
Popular Articles

Administration

Datapump : Enhanced granularity with "EXCLUDE" and "INCLUDE

User Rating:  / 3
PoorBest 

Datapump : Enhanced granularity with "EXCLUDE" and "INCLUDE".

 

See also

Datapump

Datapump Consistent

Attach to and manage running datapump jobs

Datapump new features in Oracle 11G


Traditional export gave us the possibility to do an export at

1. database level with the parameter full=Y

2. owner, user level with the parameter owner=&owner

3. table level with the parameter tables=(&owner.&table_name)

If the list of objects we wanted to export was rather long we used a parameter file and have these objects listed into this parameter file. But what when we would like to export all but a few objects ?

With the below example I exclude owner SYSMAN

 

C:\oracle\product\10.2.0\db_1\bin>expdp system/palladium parfile=c:\temp\expdp.par

 

and the contents of the parameter file c:\temp\expdp.par

 
full=y directory=export dumpfile=expdp_full_without_sysman.dmplogfile=expdp_full_without_sysman.log exclude=schema:"='SYSMAN'"

 

With the below example I show how to import only tables, indexes and views


$ORACLE_HOME/bin/impdp oper_mis/mis directory=EXPORT dumpfile=expdp_file.dmp logfile=impdp_file.log schemas=xyz remap_schema=LIMS:src_xyz include=TABLE include=INDEX include=VIEW

 

Note that it is recommended to you a parameterfile (parfile) if you would ike to use the attributes "exclude" and "include".

This is an example of a parameter file I used to import all but a few tables

 
schemas=OWNER_1
exclude=TABLE:"IN ('TABLE_1','TABLE_2','TABLE_3','TABLE_4')"
 

This is another example of a parameter file I used to export= all but a few tables
 
 
userid=exp_user/password
directory=EXPORT
dumpfile=expdp_dumpfile.dmp
logfile=expdp_dumpfile.log
content=ALL
full=y
compression=all
exclude=TABLE:"LIKE '%_AUDIT_LOG'"
FLASHBACK_SCN=8857232931189