Data Pump has a vast array of mechanisms for filtering data and metadata. You can influence what is excluded or included in a Data Pump export or import in the following ways:
- Use the QUERY parameter to export or import subsets of data.
- Use the SAMPLE parameter to export a percentage of the rows in a table.
- Use the CONTENT parameter to exclude or include data and metadata.
- Use the EXCLUDE parameter to specifically name items to be excluded.
- Use the INCLUDE parameter to name the items to be included (thereby excluding other nondependent items not included in the list).
- Use parameters such as SCHEMAS to specify that you only want a subset of the database’s objects (those that belong to the specified user or users).
Examples of each of these techniques are described in the following sections.
Note You can’t use EXCLUDE and INCLUDE at the same time. These parameters are mutually exclusive.
Specifying a Query
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 recreating 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_clauseThe query clause can be any valid SQL 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 VARCHAR2 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/foo
directory=dp_dir
dumpfile=inv.dmp
tables=inv,reg
query=inv:"WHERE inv_desc='Book'"
query=reg:"WHERE reg_id <=20"
Say you place the previous lines of code in a file named inv.par. The export job references the parameter file as shown:
$ expdp parfile=inv.parThe resulting dump file only contains rows filtered by the QUERY parameters. Again, be mindful of any parent–child relationships, and ensure that what gets exported won’t 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/foo
directory=dp_dir
dumpfile=inv.dmp
tables=inv,reg
query=inv:"WHERE inv_id > 10"
This text is placed in a file named inv2.par and is referenced during the import as follows:
$ impdp parfile=inv2.par
All the rows from the REG table are imported. Only the rows in the INV table that have an INV_ID greater than 10 are imported.
Exporting a Percentage of the Data
When exporting, the SAMPLE parameter instructs Data Pump to retrieve a certain percentage of rows, based on a number you provide. Data Pump doesn’t keep track of parent–child relationships when exporting. Therefore, this approach doesn’t work well when you have tables linked via foreign key constraints and you’re trying to select a percentage of rows randomly.
Here is the general syntax for this parameter:
SAMPLE=[[schema_name.]table_name:]sample_percentFor example, if you want to export 10 percent of the data in a table, do so as follows:
$ expdp mv_maint/foo directory=dp_dir tables=inv sample=10 dumpfile=inv.dmpThis next example exports two tables, but only 30 percent of the REG table’s data:
$ expdp mv_maint/foo directory=dp_dir tables=inv,reg sample=reg:30 dumpfile=inv.dmp Note The SAMPLE parameter is only valid for exports.
Excluding Objects from the Export File
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_type[:name_clause] [, ...]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
The EXCLUDE parameter instructs Data Pump export to filter out specific objects from the export. For instance, say you’re exporting a table but want to exclude the indexes and grants:
$ expdp mv_maint/foo directory=dp_dir dumpfile=inv.dmp tables=inv exclude=index,grantYou can filter at a more granular level by using NAME_CLAUSE. The NAME_CLAUSE option of EXCLUDE allows you to specify an SQL filter. To exclude indexes that have names that start with the string “INV”, you use the following command:
exclude=index:"LIKE 'INV%'"The previous line requires that you use quotation marks; in these scenarios, I recommend that you use a parameter file. Here is a parameter file that contains an EXCLUDE clause:
userid=mv_maint/foo
directory=dp_dir
dumpfile=inv.dmp
tables=inv
exclude=index:"LIKE 'INV%'"
A few aspects of the EXCLUDE clause may seem counterintuitive. For example, consider the following export parameter file:
userid=mv_maint/foo
directory=dp_dir
dumpfile=sch.dmp
exclude=schema:"='HEERA'"
If you attempt to exclude a user in this manner, an error is thrown. This is because the default mode of export is SCHEMA level, and Data Pump can’t exclude and include a schema at the same time. If you want to exclude a user from an export file, specify the FULL mode, and exclude the user:
userid=mv_maint/foo
directory=dp_dir
dumpfile=sch.dmp
exclude=schema:"='HEERA'"
full=y
By default, when you export a table object, any statistics are also exported. You can prevent statistics from being imported via the EXCLUDE parameter. Here is an example:
$ expdp mv_maint/foo directory=dp_dir dumpfile=inv.dmp \
tables=inv exclude=statistics
When importing, if you attempt to exclude statistics from a dump file that didn’t originally include the statistics, then you receive this error:
ORA-39168: Object path STATISTICS was not found.You also receive this error if the objects in the exported dump file never had statistics generated for them.
Including Only Specific Objects in an Export File
Use the INCLUDE parameter to include only certain database objects in the export file. The following example exports only the procedures and functions that a user owns:
$ expdp mv_maint/foo dumpfile=proc.dmp directory=dp_dir include=procedure,functionThe proc.dmp file that is created contains only the DDL required to recreate any procedures and functions the user owns.
When using INCLUDE, you can also specify that only specific PL/SQL objects should be exported:
$ expdp mv_maint/foo directory=dp_dir dumpfile=ss.dmp \
include=function:\"=\'IS_DATE\'\"
When you’re exporting only specific PL/SQL objects, because of the issue of having to escape quotation marks on the OS command line, I recommend using a parameter file. When you use a parameter file, this is not a concern. The following example shows the contents of a parameter file that exports specific objects:
directory=dp_dir
dumpfile=ss.dmp
include=function:"='ISDATE'",procedure:"='DEPTREE_FILL'"
If you specify an object that doesn’t exist, Data Pump throws an error but continues with the export operation:
ORA-39168: Object path FUNCTION was not found.Exporting Table, Index, Constraint, and Trigger DDL
Suppose you want to export the DDL associated with tables, indexes, constraints, and triggers in your database. To do this, use the FULL export mode, specify CONTENT=METADATA_ONLY, and only include tables:
$ expdp mv_maint/foo directory=dp_dir dumpfile=ddl.dmp \
content=metadata_only full=y include=table
When you export an object, Data Pump also exports any dependent objects. So, when you export a table, you also get indexes, constraints, and triggers associated with the table.
Excluding Objects from Import
In general, you can use the same techniques used to filter objects in exports to exclude objects from being imported. Use the EXCLUDE parameter to exclude objects from being imported. For example, to exclude triggers and procedures from being imported, use this command:
$ impdp mv_maint/foo dumpfile=inv.dmp directory=dp_dir exclude=TRIGGER,PROCEDUREYou can further refine what is excluded by adding an SQL clause. For example, say you want not to import triggers that begin with the letter B. Here is what the parameter file looks like:
userid=mv_maint/foo
directory=dp_dir
dumpfile=inv.dmp
schemas=HEERA
exclude=trigger:"like 'B%'"
Including Objects in Import
You can use the INCLUDE parameter to reduce what is imported. Suppose you have a schema from which you want to import tables that begin with the letter A. Here is the parameter file:
userid=mv_maint/foo
directory=dp_dir
dumpfile=inv.dmp
schemas=HEERA
include=table:"like 'A%'"
If you place the previous text in a file named h.par, then the parameter file can be invoked as follows:
$ impdp parfile=h.parIn this example the HEERA schema must already exist. Only tables that start with the letter A are imported.
