Saturday, April 5, 2008

DataPump Parameters INCLUDE and EXCLUDE - How to Load and Unload Specific Objects

With the export/import new technology data pump we can filter to load/unload certain objects. This so-called 'Metadata filtering' is implemented through the EXCLUDE and INCLUDE parameters.
Incorrect usage of metadata filters, can result in errors such as:

ORA-39001: invalid argument value
ORA-31655: no data or metadata objects selected for job
UDE-00011: parameter include is incompatible with parameter exclude


How metadata can be filtered with the EXCLUDE and INCLUDE parameters.


1. Syntax of the INCLUDE and EXCLUDE Data Pump parameters.


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

EXCLUDE=SEQUENCE, TABLE:"IN ('EMP', 'DEPT')"
INCLUDE=FUNCTION, PACKAGE, TABLE:"= 'EMP'"

2. SQL Operator usage.

EXCLUDE=TABLE:"IN ('EMP', 'DEPT')"

or:

EXCLUDE=INDEX:"= 'MY_INDX'"

or:

INCLUDE=PROCEDURE:"LIKE 'MY_PROC_%'"

or:

INCLUDE=TABLE:"> 'E'"

3.Double quotes and single quotes usage.

The name clause is separated from the object type with a colon. The name clause must be enclosed in double quotation marks. The single-quotation marks are required to delimit the name strings. Using the INCLUDE or EXCLUDE parameter in a parameter file is the preferred method.
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

To run this job without a parameter file, you need to escape the special characters. Incorrect escaping can result in errors such as: ksh: syntax error: '(' unexpected.
expdp system/manager DIRECTORY=my_dir \
DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott \
INCLUDE=TABLE:\"IN \(\'EMP\', \'DEP\'\)\"


4. Using the same filter name for an object type more than once.

If multiple filters are specified for an object type, an implicit AND operation is applied to them. That is, the objects that are exported or imported during the job have passed all of the filters applied to their object types.

5. The EXCLUDE and INCLUDE parameters are mutually exclusive.


It is not possible to specify both the INCLUDE parameter and the EXCLUDE parameter in the same job.
Parameter file - incorrect syntax (error: UDE-00011):

INCLUDE=TABLE:"IN ('EMP', 'DEPT')"
EXCLUDE=INDEX:"= 'PK_EMP'"

Parameter file - correct syntax:

INCLUDE=TABLE:"IN ('EMP', 'DEPT')"


6. Specified object types depend on the export/import Data Pump mode.
During a TABLE level export/import, certain object types that are directly related to SCHEMA or DATABASE level jobs, cannot be specified. The same applies to a SCHEMA level export/import where no DATABASE level object types can be specified.

To determine the name of the object types can be specified with EXCLUDE and INCLUDE,

database_export_objects
schema_export_objects
table_export_objects

7. Only specific object types can be named with a Name clause.


The name clause applies only to object types whose instances have names (for example, it is applicable to TABLE, but not to GRANT).
To determine which object types can be named, you can use,database_export_objects and ....

EXCLUDE = TRIGGER:"IN ('TRIG1', 'TRIG2')", INDEX:"= 'INDX1'", REF_CONSTRAINT

8. Excluding/Including an object, will also exclude/include it's dependent objects.

Dependent objects of an identified object are processed along with the identified object. For example, if a filter specifies that an index is to be included in an operation, then statistics from that index will also be included. Likewise, if a table is excluded by a filter, then indexes, constraints, grants, and triggers upon the table will also be excluded by the filter.

To determine which objects are dependent, e.g. for a TABLE, you can run the following query,
SELECT named, object_path, comments
FROM database_export_objects
WHERE object_path LIKE 'TABLE/%';


9. Exporting or Importing a large number of objects.


If a large number of objects needs to be exported (or imported), it is possible that an internal buffer limit is exceeded (e.g. for the parameters INCLUDE or TABLES). If that happens it will cause the Data Pump job to abort with an error such as: ORA-06502 (PL/SQL: numeric or value error: character string buffer too small). This happened especially in Oracle10g Release 1 where the value for the internal buffer was set to 3000 bytes. With the fix for Bug 4053129 "EXPDP fails with ORA-39125 ORA-6502 on large list of table names" (not a public bug; fixed in 10.1.0.5.0 and 10.2.0.x), this value was increased to 4000 bytes.
When exporting a large number of objects, we recommend to make use of a table inside the database that contains the names of the objects.
Example:
-- create a table that contains the names of the objects:

CONNECT scott/tiger
CREATE TABLE expdp_table (owner VARCHAR2(30),
object_name VARCHAR2(128), object_type VARCHAR2(19));
INSERT INTO expdp_table VALUES ('SCOTT','EMP','TABLE');
INSERT INTO expdp_table VALUES ('SCOTT','DEPT','TABLE');
INSERT INTO expdp_table VALUES ('SCOTT','BONUS','TABLE');
...
COMMIT;
-- run export DataPump job:

expdp system/manager DIRECTORY=my_dir \
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\'\)\"

On windows in one line following is valid.
expdp system/a SCHEMAS=aRJU INCLUDE=TABLE:\"IN \(SELECT object_name FROM dba_objects WHERE owner=\'ARJU\' AND object_type=\'TABLE\'\)\"

10. Other issues when excluding objects at a Data Pump job.

10.1. Excluding Constraints.
The following constraints cannot be excluded:
- NOT NULL constraints.
- Constraints needed for the table to be created and loaded successfully (for example, primary key constraints for index-organized tables or REF SCOPE and WITH ROWID constraints for tables with REF columns).

10.2. Excluding Grants.
Specifying EXCLUDE=GRANT excludes object grants on all object types and system privilege grants.

10.3. 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 you want to exclude):
EXCLUDE=SCHEMA:"='SCOTT'"

If you try to exclude a user by using a statement such as EXCLUDE=USER:"= 'SCOTT'", only the CREATE USER scott DDL statement will be excluded, and you may not get the results you expect.
11. Other issues when including objects at a Data Pump job.

When specifying the INCLUDE parameter for en Export Data Pump or Import Data Pump job, only object types explicitly specified in INCLUDE statements (and their dependent objects) are exported/imported. No other object types, such as the schema definition information that is normally part of a schema-mode export when you have the EXP_FULL_DATABASE role, are exported/imported.

Related Documents
http://arjudba.blogspot.com/2009/02/important-guideline-about-data-pump.html
http://arjudba.blogspot.com/2009/02/how-to-do-data-pump-import-from-higher.html
http://arjudba.blogspot.com/2008/04/data-pump-exportimport-how-we-can-do-it.html
http://arjudba.blogspot.com/2009/02/ude-00018-data-pump-client-is.html
http://arjudba.blogspot.com/2008/04/exportimport-datapump-parameter-query.html
http://arjudba.blogspot.com/2008/04/improve-data-pump-performance.html
http://arjudba.blogspot.com/2009/02/how-to-take-data-pump-export-on-another.html
http://arjudba.blogspot.com/2009/02/compatibility-table-of-oracle-data-pump.html

14 comments:

Ryan said...

Thank you for being the only web page that could help me with my problem.

You pointed out the necessity for the escape characters in the exclude=tables: syntax.

Arju said...

thank you for visiting my blog. Keep visiting.

Samer said...

I agree, good stuff man!

Shakil said...

I am visiting your blog. Thank you.

Raghu said...

Thank you, this post was useful.

Yi said...

Hi Arju,

I tried your instruction:
CONNECT scott/tiger
CREATE TABLE expdp_tab (owner VARCHAR2(30),
object_name VARCHAR2(128), object_type VARCHAR2(19));
INSERT INTO expdp_tab VALUES ('SCOTT','EMP','TABLE');
INSERT INTO expdp_tab VALUES ('SCOTT','DEPT','TABLE');
INSERT INTO expdp_tab VALUES ('SCOTT','BONUS','TABLE');
...
COMMIT;
-- run export DataPump job:

expdp system/manager DIRECTORY=my_dir \
DUMPFILE=expdp_s.dmp LOGFILE=expdp_s.log SCHEMAS=scott \
INCLUDE=TABLE:\"IN \(SELECT object_name FROM scott.expdp_tab WHERE \
owner=\'SCOTT\' AND object_type=\'TABLE\'\)\"

But when I ran it, I always got the following error:
LRM-00101: unknown parameter name 'owner'

Here is my parfile:
INCLUDE=TABLE:\"IN\(SELECT object_name FROM test.expdp_tab WHERE owner=\'test\' AND object_type=\'TABLE\'\)\"


Please advice me why I receive this error.
Thank you very much,
Amos

Arju said...

It worked for me. May be you are missing something.

Arju said...

If you put it inside parfile then no escape character will be included. So remove all backslash (\). However if you want to run it from console/shell/command prompt then escape characters are needed.

In this case I could not understand your requirement. Are you using parfile?

Yi said...

Hi Arju,
Here is my parfile:
SCHEMAS=TEST
INCLUDE=TABLE:"IN (SELECT object_name FROM ingest.expdp_tab WHERE owner='TEST' AND object_type='TABLE')"
DIRECTORY=DATAPUMP




But after I ran the expdp command, I got the following errors:
Starting "SYSTEM"."EXPDP_TEST_JOB": system/******** parfile=exp.par dumpfile=expdp_TEST-032909_07_21.dmp logfile=expdp_TEST-032909_07_21.log job_name=expdp_TEST_job
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
ORA-39165: Schema TEST was not found.
ORA-39168: Object path TABLE was not found.
ORA-31655: no data or metadata objects selected for job
Job "SYSTEM"."EXPDP_TEST_JOB" completed with 3 error(s) at 07:22:02


Thanks,
Yi

Yi said...

Hi Arju,

It works. The owner name I used should not be "test", after I changed it to the right username, the script works.
Thank you very much.
YI

Dianna said...

Arju,
How would I specify a list of tables with filters to include? It does not like this:
INCLUDE=TABLE:"LIKE 'MDL_%'",TABLE:"='CLUSTER_LIST'",TABLE:"LIKE '%BINCAT'",TABLE:"LIKE '%NORM'",TABLE:"LIKE '%SET'"
The error I get is:
ORA-39168: Object path TABLE was not found.

Thanks,
Dianna

Dharmendra Singh said...

very nice job

admin said...

Very nice information. I have also written a article Expdp Exclude and Expdp Include Parameter.

Please see at http://www.dbas-oracle.com/2013/07/Expdp-Exclude-and-Expdp-Include-Parameter.html

Anonymous said...

Very useful, thanks.