EXPDP DATAPUMP EXCLUDE/INCLUDE parameters
The exclude and include parameters availbale with expdp,impdp can be used as metadata filters so that one can specify any objects like tables,indexes,triggers, procedure to be excluded or included during export or import operation
syntax:
EXCLUDE=[object_type]:[name_clause],[object_type]:[name_clause]
INCLUDE=[object_type]:[name_clause],[object_type]:[name_clause]
examples:
expdp <other_parameters> SCHEMAS=scott EXCLUDE=SEQUENCE,TABLE:”IN (’EMP’,'DEPT’)”;
impdp <other_parameters> SCHEMAS=scott INCLUDE=PACKAGE,FUNCTION, PROCEDURE,TABLE:”=’EMP’”
The name_clause is a SQL expression that is used as a filter on the object names of the object. It consists of a SQL operator and the values against which the object names of the specified type are to be compared. If no name_clause is provided, all objects of the specified type are excluded/included. The name clause must be separated from the object type with a colon.
Examples of operator-usage:
EXCLUDE=SEQUENCE
or:EXCLUDE=TABLE:”IN (’EMP’,'DEPT’)”
or:EXCLUDE=INDEX:”= ‘MY_INDX’”
or:INCLUDE=PROCEDURE:”LIKE ‘MY_PROC_%’”
or:INCLUDE=TABLE:”> ‘E’”
The parameter can also be stored in a par (parameter file) as shown
Parameter file:exp.par
DIRECTORY = my_dir
DUMPFILE = exp_tab.dmp
LOGFILE = exp_tab.log
SCHEMAS = scott
INCLUDE = TABLE:”IN (’EMP’, ‘DEPT’)”
expdp system/manager parfile=exp.par
If parameter file is not used then in unix special care needs to be taken in syntax of expdp and impdp, in particular all the single quotes and double quotes needs to be preceded with the special character ‘\’ .The syntax for windows and unix
Windows:
D:\> expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott INCLUDE=TABLE:\”IN (’EMP’, ‘DEP’)\”
Unix:
% expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott INCLUDE=TABLE:\”IN \(\’EMP\’, \’DEP\’\)\”
Any improper use of exclude or include can give you any of the below mentioned errors and hence to avoid this error please be careful and read the entire post carefully .
ORA-39001: invalid argument value
ORA-39071: Value for INCLUDE is badly formed.
ORA-00936: missing expression
or:
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00904: “DEPT”: invalid identifier
or:
ORA-39001: invalid argument value
ORA-39041: Filter “INCLUDE” either identifies all object types or no object types.
or:
ORA-39001: invalid argument value
ORA-39041: Filter “EXCLUDE” either identifies all object types or no object types
.or:
ORA-39001: invalid argument value
ORA-39038: Object path “USER” is not supported for TABLE jobs.
or:
UDE-00011: parameter include is incompatible with parameter exclude
or:
ksh: syntax error: ‘(’ unexpected
or:
ORA-31655: no data or metadata objects selected for job
Thanks and Regards
Parikshit
All,
When using includes or excludes in windows with the LIKE function with a wild card, you may have to use double wild cards to get it to work.
Example: INCLUDE=table:\”like ‘TABS_%%’\”
Dan
Thanks for the information. This helped me fix my problem.
It is interesting to see that it was not described in oracle documentation site about the details. I didn’t expect that we need escapes on single quotes. Thanks.
I have one question:
How do I exclude object types that have a blank in the name, like DATABASE LINK or MATERIALIZED VIEW?
I tried (in a parameterfile to avoid escaping problems on th command line):
exclude=”DATABASE LINK”
exclude=’DATABASE LINK’
exclude=DATABASE\ LINK
exclude=DATABASE_LINK
exclude=DATABASELINK
exclude=LINK
and none of them worked…, giving only a
ORA-39041: Filter “EXCLUDE” either identifies all object types or no object types.
How does one exclude objects of type DATABASE LINK or MATERIALIZED VIEW?
Thanks,
Peter
Hi Peter,
you can use exclude=DB_LINK,materialized_view to exclude database link and materlialized views
Is it possible to include a table structure but exclude the table data ?
Hi Taharban,
You can use ROWS=n argument of export exp utility to capture only the table structure
Hi
How user-defined objects can be excluded from the export dump file?
I tried exclude=\”IN \(\’my_obj\’,\’my_tab\’\)\”
and I got the following errors:
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00907: missing right parenthesis
thanks.
To exclude only the data of a particular table:
QUERY=MY_TABLE:”WHERE 1 = 2″
On Unix I tried this and it worked fine. The only thing needs to be kept in mind is that object name has to be in upper case.
EXCLUDE=TABLE:\”IN \(\’EMP\’,\’DEPT\’\)\”
Thanks
I am an MCPD and i really appreciate that professional answer.
Hi. I have a problem, I want to make an export using the LIKE operator, but I have two conditions, I want to include the tables
EMPLOYEES, DEPARTMENT,JOBS and JOB_HISTORY, I know how to do it with IN, but I have to use LIKE, I have this, but it generates errors
expdp system/oracle DIRECTORY=my_dir SCHEMAS=HR INCLUDE=TABLE:”LIKE’%JOB%’”OR LIKE’DEPAR%’ OR LIKE ‘EMP%’” DUMPFILE=backup_like3.dmp logfile=log_like3.log
I’ll appreciate your help.
i have an error including operate expdp ,just like
expdp scott/tiger dumpfile=scott.dmp directory = dtdump logfile=logfile.log include=table:\” in (’A',’AA’)\”
Alejandro on July 23rd, 2009 at 12:17 am
perhaps you can use this sql to filte the tables: just like regexp_like(table_name,’(*JOB*|*DEPAR*|*EMP*)’) ,but first we should to know whether the include=table:”" clause depends on data directory…