3.3 Filtering During Import Operations

Oracle Data Pump Import provides data and metadata filtering capability, which can help you limit the type of information that you import.

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

3.3.1 Oracle Data Pump Import Data Filters

You can specify restrictions on the table rows that you import 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.

3.3.2 Oracle Data Pump Import Metadata Filters

To exclude or include objects in an import 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 Oracle Data Pump operation. For example: You can request a full import, but without Package Specifications or Package Bodies. Oracle Data Pump Import provides much greater metadata filtering capability than was provided by the original Import utility.

To use filters correctly, and to obtain the results that you expect, remember that dependent objects of an identified object are processed along with the identified object.

For example, if a filter specifies that a package is to be included in an operation, then grants upon that package 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.

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, and includes all objects identified by the parameter. Then it processes the exclude parameters. Specifically, the EXCLUDE_PATH_EXPR, EXCLUDE_PATH_LIST and EXCLUDE_TABLE parameters are processed last. Any objects specified by the EXCLUDE parameter that are in the list of include objects are removed as the command executes.

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

The same filter name can be specified 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. Note that full object path names are determined by the export mode, not by the import mode.