Database Reference
In-Depth Information
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,grant
You 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'"
