Export/Import DataPump Parameters INCLUDE and EXCLUDE - How to Load and Unload Specific Objects
(Doc ID 341733.1)
Last updated on JULY 24, 2024
Applies to:
Oracle Database - Standard Edition - Version 10.1.0.2 and laterEnterprise 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-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-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-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-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-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.
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-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 |