Default: There is no default
Purpose
Enables you to filter the metadata that is imported by specifying objects and object types to exclude from the import job.
Syntax and Description
EXCLUDE=object_type[:name_clause] [, ...]
The object_type
specifies the type of object to be excluded. To see a list of valid values for object_type
, query the following views: DATABASE_EXPORT_OBJECTS
for full mode, SCHEMA_EXPORT_OBJECTS
for schema mode, and TABLE_EXPORT_OBJECTS
for table and tablespace mode. The values listed in the OBJECT_PATH
column are the valid object types. (See "Metadata Filters" for an example of how to perform such a query.)
For the given mode of import, all object types contained within the source (and their dependents) are included, except those specified in an EXCLUDE
statement. If an object is excluded, then all of its dependent objects are also excluded. For example, excluding a table will also exclude all indexes and triggers on the table.
The name_clause
is optional. It allows fine-grained selection of specific objects within an object type. It is a SQL expression used as a filter on the object names of the type. It consists of a SQL operator and the values against which the object names of the specified type are to be compared. The name_clause
applies only to object types whose instances have names (for example, it is applicable to TABLE
and VIEW
, but not to GRANT
). It must be separated from the object type with a colon and enclosed in double quotation marks, because single quotation marks are required to delimit the name strings. For example, you could set EXCLUDE=INDEX:"LIKE
'DEPT%'"
to exclude all indexes whose names start with dept
.
The name that you supply for the name_clause
must exactly match, including upper and lower casing, an existing object in the database. For example, if the name_clause
you supply is for a table named EMPLOYEES
, then there must be an existing table named EMPLOYEES
using all upper case. If the name_clause
were supplied as Employees
or employees
or any other variation, then the table would not be found.
More than one EXCLUDE
statement can be specified.
Depending on your operating system, the use of quotation marks when you specify a value for this parameter may also require that you use escape characters. Oracle recommends that you place this parameter in a parameter file, which can reduce the number of escape characters that might otherwise be needed on the command line.
As explained in the following sections, you should be aware of the effects of specifying certain objects for exclusion, in particular, CONSTRAINT
, GRANT
, and USER
.
Excluding Constraints
The following constraints cannot be excluded:
NOT
NULL
constraints.
Constraints needed for the table to be created and loaded successfully (for example, primary key constraints for index-organized tables or REF
SCOPE
and WITH
ROWID
constraints for tables with REF
columns).
This means that the following EXCLUDE
statements will be interpreted as follows:
EXCLUDE=
CONSTRAINT
will exclude all nonreferential constraints, except for NOT
NULL
constraints and any constraints needed for successful table creation and loading.
EXCLUDE=
REF_CONSTRAINT
will exclude referential integrity (foreign key) constraints.
Excluding Grants and Users
Specifying EXCLUDE=
GRANT
excludes object grants on all object types and system privilege grants.
Specifying EXCLUDE=
USER
excludes only the definitions of users, not the objects contained within users' schemas.
To exclude a specific user and all objects of that user, specify a command such as the following, where hr
is the schema name of the user you want to exclude.
impdp FULL=YES DUMPFILE=expfull.dmp EXCLUDE=SCHEMA:"='HR'"
Note that in this situation, an import mode of FULL
is specified. If no mode were specified, then the default mode, SCHEMAS
, would be used. This would cause an error because the command would indicate that the schema should be both imported and excluded at the same time.
If you try to exclude a user by using a statement such as EXCLUDE
=USER
:"= 'HR
'", then only CREATE USER hr
DDL statements will be excluded, and you may not get the results you expect.
Restrictions
The EXCLUDE
and INCLUDE
parameters are mutually exclusive.
Example
Assume the following is in a parameter file, exclude.par
, being used by a DBA or some other user with the DATAPUMP_IMP_FULL_DATABASE
role. (If you want to try the example, then you must create this file.)
EXCLUDE=FUNCTION EXCLUDE=PROCEDURE EXCLUDE=PACKAGE EXCLUDE=INDEX:"LIKE 'EMP%' "
You could then issue the following command. You can create the expfull.dmp
dump file used in this command by running the example provided for the Export FULL
parameter. See "FULL".
> impdp system DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp PARFILE=exclude.par
All data from the expfull.dmp
dump file will be loaded except for functions, procedures, packages, and indexes whose names start with emp
.
"Filtering During Import Operations" for more information about the effects of using the EXCLUDE
parameter