2.3 Filtering During Export Operations

Oracle Data Pump Export provides data and metadata filtering capability. This capability helps you limit the type of information that is exported.

• Oracle Data Pump Export Data Filters
  You can specify restrictions on the table rows that you export by using Oracle Data Pump Data-specific filtering through the QUERY and SAMPLE parameters.
• Oracle Data Pump Metadata Filters
  To exclude or include objects in an export operation, use Oracle Data Pump metadata filters

2.3.1 Oracle Data Pump Export Data Filters

You can specify restrictions on the table rows that you export by using Oracle Data Pump Data-specific filtering through the QUERY and SAMPLE parameters.

Oracle Data Pump can also implement Data filtering indirectly because of metadata filtering, which can include or exclude table objects along with any associated row data.

Each data filter can be specified once for each table within a job. If different filters using the same name are applied to both a particular table and to the whole job, then the filter parameter supplied for the specific table takes precedence.

2.3.2 Oracle Data Pump Metadata Filters

To exclude or include objects in an export operation, use Oracle Data Pump metadata filters

Metadata filtering is implemented through the EXCLUDE and INCLUDE parameters. Metadata filters identify a set of objects that you want to be included or excluded from an Export or Import operation. For example, you can request a full export, but without Package Specifications or Package Bodies.

To use filters correctly and to obtain the results you expect, remember that dependent objects of an identified object are processed along with the identified object. For example, if a filter specifies that you want an index included in an operation, then statistics from that index are also included. Likewise, if a table is excluded by a filter, then indexes, constraints, grants, and triggers upon the table are also excluded by the filter.

Starting with Oracle Database 21c, Oracle Data Pump permits you to set both INCLUDE and EXCLUDE parameters in the same command. When you include both parameters in a command, Oracle Data Pump processes the INCLUDE parameter first, such that the Oracle Data Pump job includes only objects identified as included. Then it processes the EXCLUDE parameters, which can further restrict the objects processed by the job. As the command runs, any objects specified by the EXCLUDE parameter that are in the list of INCLUDE objects are removed.

If multiple filters are specified for an object type, then an implicit AND operation is applied to them. That is, objects pertaining to the job must pass all of the filters applied to their object types.

You can specify the same metadata filter name multiple times within a job.

To see a list of valid object types, query the following views: DATABASE_EXPORT_OBJECTS for full mode, SCHEMA_EXPORT_OBJECTS for schema mode, TABLE_EXPORT_OBJECTS for table mode, TABLESPACE_EXPORT_OBJECTS for tablespace mode and TRANSPORTABLE_EXPORT_OBJECTS for transportable tablespace mode. The values listed in the OBJECT_PATH column are the valid object types. For example, you could perform the following query:

SQL> SELECT OBJECT_PATH, COMMENTS FROM SCHEMA_EXPORT_OBJECTS
  2  WHERE OBJECT_PATH LIKE '%GRANT' AND OBJECT_PATH NOT LIKE '%/%';

The output of this query looks similar to the following:

OBJECT_PATH
--------------------------------------------------------------------------------
COMMENTS
--------------------------------------------------------------------------------
GRANT
Object grants on the selected tables
 
OBJECT_GRANT
Object grants on the selected tables
 
PROCDEPOBJ_GRANT
Grants on instance procedural objects
 
PROCOBJ_GRANT
Schema procedural object grants in the selected schemas
 
ROLE_GRANT
Role grants to users associated with the selected schemas
 
SYSTEM_GRANT
System privileges granted to users associated with the selected schemas