Database Reference
In-Depth Information
Filtering Data and Objects
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:
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 the
SCHEMAS to specify that you only want a subset of the database's
objects (those that belong to the specified user or users).
Use parameters such as
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_clause
The 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.par
 
 
Search WWH ::




Custom Search