Exclude and Include parameter of Data Pump

The Exclude and Include feature of Data Pump can filter to load/unload certain objects .This so-called 'Metadata filtering' is implemented through the EXCLUDE and INCLUDE parameters.The INCLUDE and EXCLUDE parameters can be used to limit the export/import to specific objects. 

All object types for the given mode of export will be included except those specified in an EXCLUDE statement. If an object is excluded, all of its dependent objects are also excluded. For example, excluding a table will also exclude all indexes and triggers on the table.The name clause must be separated from the object type  with a colon and enclosed in double quotation marks, because single-quotation marks are required to delimit the name strings Double quotes and single quotes usage. 

The EXCLUDE and INCLUDE parameters are mutually exclusive i.e, It is not possible to specify both the INCLUDE parameter and the EXCLUDE parameter in the same job.

The basic syntax for both parameters is the same.

INCLUDE=object_type[:name_clause] [, ...]
EXCLUDE=object_type[:name_clause] [, ...]

Few examples are :

EXCLUDE=SEQUENCE, TABLE:"IN ('EMP', 'DEPT')"
EXCLUDE=INDEX:"= 'MY_INDX'"
INCLUDE=PROCEDURE:"LIKE 'MY_PROC_%'"
INCLUDE=FUNCTION, PACKAGE, TABLE:"= 'EMP'" 
INCLUDE=PROCEDURE:\"=\'PROC1\'\",FUNCTION:\"=\'FUNC1\'\"

To run this job without a parameter file, we need to escape the special characters. Incorrect escaping can result in errors.

for example  :   INCLUDE=TABLE:\"IN \(\'EMP\', \'DEP\'\)\"

To determine which objects are dependent, e.g. for a TABLE, we can run the following query,

SQL> select  named, object_path, comments FROM database_export_objects WHERE object_path LIKE 'TABLE/%';

While Excluding Users specifying EXCLUDE=USER excludes only the definitions of users, not the objects contained within users' schemas. To exclude a specific user and all objects of that user, specify a filter such as the following (where SCOTT is the schema name of the user we want to exclude):

EXCLUDE=SCHEMA:\"='SCOTT'\"

So, we can export as also use below command to execute export .

C:\> expdp system/xxxx directory=datapump dumpfile=expdp_scott.dmp  logfile=expdp_scott.log schemas=scott  INCLUDE=TABLE:\"IN \(SELECT object_name FROM scott.expdp_table WHERE owner=\'SCOTT\' AND object_type=\'TABLE\'\)\"

Enjoy   J J J