EXPDP DATAPUMP EXCLUDE/INCLUDE parameters

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 SCHEMAS=scott EXCLUDE=SEQUENCE,TABLE:”IN (’EMP’,'DEPT’)”;

impdp 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