My Oracle Support Banner

Export/Import DataPump Parameters INCLUDE and EXCLUDE - How to Load and Unload Specific Objects (Doc ID 341733.1)

Last updated on MARCH 26, 2019

Applies to:

Oracle Database - Standard Edition - Version 10.1.0.2 and later
Enterprise Manager for Oracle Database - Version 10.1.0.2 to 12.1.0.4.0 [Release 10.1 to 12.1]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
NOTE: In the images and/or the document content below, the user information and data used represents fictitious data. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

Goal

This document demonstrates how to load and unload certain objects with the Oracle10g, Oracle11g, and Oracle12c Export DataPump and Import DataPump utilities. 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-39071: Value for INCLUDE is badly formed.
ORA-00936: missing expression
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00920: invalid relational operator
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00904: "DEPT": invalid identifier
ORA-39001: invalid argument value
ORA-39041: Filter "INCLUDE" either identifies all object types or no object types.
ORA-39001: invalid argument value
ORA-39041: Filter "EXCLUDE" either identifies all object types or no object types.
ORA-39001: invalid argument value
ORA-39038: Object path "USER" is not supported for TABLE jobs.
UDE-00011: parameter include is incompatible with parameter exclude
ksh: syntax error: '(' unexpected
ORA-39165: Schema <schema_name> was not found.
ORA-39168: Object path <path_name> was not found.
ORA-31655: no data or metadata objects selected for job

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution
 1. Syntax of the INCLUDE and EXCLUDE Data Pump parameters.
 2. SQL Operator usage.
 3. Double quotes and single quotes usage.
 4. Using the same filter name for an object type more than once.
 5. The EXCLUDE and INCLUDE parameters are mutually exclusive.
 6. Specified object types depend on the export/import Data Pump mode.
 7. Only specific object types can be named with a Name clause.
 8. Excluding/Including an object, will also exclude/include it's dependent objects.
 9. Exporting or Importing a large number of objects.
 10. Other issues when excluding objects at a Data Pump job.
 11. Other issues when including objects at a Data Pump job.
 Additional Resources
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.