Administration
Datapump : Enhanced granularity with "EXCLUDE" and "INCLUDE
User Rating: / 3
- Details
-
Parent Category: Articles
-
Created on Wednesday, 24 April 2013 15:33
-
Last Updated on Wednesday, 25 March 2015 11:25
-
Published on Wednesday, 24 April 2013 15:38
-
Written by Guy Lambregts
-
Hits: 21902
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.dmp
logfile=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