Specifying a Query- External Tables

You can use the QUERY parameter to instruct Data Pump to write to a dump file only rows that meet a certain criterion.

You may want to do this if you’re re-creating a test environment and only need subsets of the data.

Keep in mind that this technique is unaware of any foreign key constraints that may be in place, so you can’t blindly restrict the data sets without considering parent-child relationships.

The QUERY parameter has this general syntax for including a query:

QUERY = [schema.][table_name:] query_clause

The query clause can be any valid WHERE clause. The query must be enclosed by either double or single quotation marks. I recommend using double quotation marks because you may need to have single quotation marks embedded in the query to handle VACHAR2 data.

Also, you should use a parameter file so that there is no confusion about how the OS interprets the quotation marks.

This example uses a parameter file and limits the rows exported for two tables. Here is the parameter file used when exporting:

userid=mv_maint/Pa$$w0rd123! directory=dp_dir dumpfile=inv.dmp tables=inv query=inv:”WHERE inv_desc=’Book’”

The previous lines are in the parameter file inv.par. The export job references the parameter file as shown here:

$ expdp parfile=inv.par

The resulting dump file only contains rows filtered by the QUERY parameters. Again, be mindful of any parent-child relationship, and ensure that what gets exported will not violate any constraints on the import.

You can also specify a query when importing data. Here is a parameter file that limits the rows imported into the INV table, based on the INV_ID column:

userid=mv_maint/Pa$$w0rd123! directory=dp_dir dumpfile=inv.dmp

tables=inv query=inv:”WHERE inv_id > 10″

The import job references the parameter file as shown here:

$ impdp parfile=inv2.par

Exclude Objects from Export or Import

For export, the EXCLUDE parameter instructs Data Pump not to export specified objects (whereas the INCLUDE parameter instructs Data Pump to include only specific objects in the export file). The EXCLUDE parameter has this general syntax:

EXCLUDE=object_typ [:name_cluase][, …]

The OBJECT_TYPE is a database object, such as TABLE or INDEX. To see which object types can be filtered, view the OBJECT_PATH column of DATABASE_EXPORT_OBJECTS, SCHEMA_EXPORT_OBJECTS, or TABLE_EXPORT_OBJECTS. For example, if you want to view what schema-level objects can be filtered, run this query:

select object_path from schema_export_objects where object_path not like ‘%/%’;

Here is a snippet of the output:

OBJECT_PATH

STATISTICS SYNONYM SYSTEM_GRANT TABLE TABLESPACE_QUOTA TRIGGER

This EXCLUDE parameter example says that you are exporting a table but want to exclude the indexes and grants:

$ expdp mv_maint/Pa$$w0rd123! directory=dp_dir dumpfile=inv.dmp tables=inv exclude=index,grant

You can filter at a more granular level by using NAME_CLAUSE. The NAME_CLAUSE option of EXCLUDE allows you to specify a SQL filter.