Default: There is no default
Syntax and Description
EXCLUDE=object_type[:name_clause] [, ...]
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.)
All object types for the given mode of export are included in the export 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.
name_clause is optional. It allows selection of specific objects within an object type. It is a SQL expression used as a filter on the type's object names. 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, 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 'EMP%'" to exclude all indexes whose names start with
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 any other variation, then the table would not be found.
name_clause is provided, then all objects of the specified type are excluded.
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.
object_type you specify is
USER, then you should be aware of the effects, as described in the following paragraphs.
The following constraints cannot be explicitly excluded:
Constraints needed for the table to be created and loaded successfully; for example, primary key constraints for index-organized tables, or
ROWID constraints for tables with
This means that the following
EXCLUDE statements are interpreted as follows:
EXCLUDE=CONSTRAINT excludes all constraints, except for any constraints needed for successful table creation and loading.
EXCLUDE=REF_CONSTRAINT excludes referential integrity (foreign key) constraints.
Excluding Grants and Users
GRANT excludes object grants on all object types and system privilege grants.
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.
expdp FULL=YES DUMPFILE=expfull.dmp EXCLUDE=SCHEMA:"='HR'"
Note that in this situation, an export 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 exported and excluded at the same time.
If you try to exclude a user by using a statement such as
EXCLUDE=USER:"='HR'", then only the information used in
CREATE USER hr DDL statements is excluded, and you may not get the results you expect.
INCLUDE parameters are mutually exclusive.
The following is an example of using the
> expdp hr DIRECTORY=dpump_dir1 DUMPFILE=hr_exclude.dmp EXCLUDE=VIEW, PACKAGE, FUNCTION
This results in a schema-mode export (the default export mode) in which all of the
hr schema is exported except its views, packages, and functions.
"Filtering During Export Operations" for more information about the effects of using the
"INCLUDE" for an example of using a parameter file