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

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

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.

Get Oracle Certifications for all Exams
Free Online Exams.com