Default: There is no default
Purpose
Enables you to filter the metadata that is exported by specifying objects and object types for the current export mode. The specified objects and all their dependent objects are exported. Grants on these objects are also exported.
Syntax and Description
INCLUDE = object_type[:name_clause] [, ...]
The object_type
specifies the type of object to be included. 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.)
Only object types explicitly specified in INCLUDE
statements, and their dependent objects, are exported. No other object types, including the schema definition information that is normally part of a schema-mode export when you have the DATAPUMP_EXP_FULL_DATABASE
role, are exported.
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
, 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.
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.
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. See "Use of Quotation Marks On the Data Pump Command Line".
For example, suppose you have a parameter file named hr.par
with the following content:
SCHEMAS=HR DUMPFILE=expinclude.dmp DIRECTORY=dpump_dir1 LOGFILE=expinclude.log INCLUDE=TABLE:"IN ('EMPLOYEES', 'DEPARTMENTS')" INCLUDE=PROCEDURE INCLUDE=INDEX:"LIKE 'EMP%'"
You could then use the hr.par
file to start an export operation, without having to enter any other parameters on the command line. The EMPLOYEES
and DEPARTMENTS
tables, all procedures, and all index names with an EMP prefix will be included in the export.
> expdp hr PARFILE=hr.par
Including Constraints
If the object_type
you specify is a CONSTRAINT
, then you should be aware of the effects this will have.
The following constraints cannot be explicitly included:
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 INCLUDE
statements will be interpreted as follows:
INCLUDE=
CONSTRAINT
will include all (nonreferential) constraints, except for NOT
NULL
constraints and any constraints needed for successful table creation and loading
INCLUDE=
REF_CONSTRAINT
will include referential integrity (foreign key) constraints
Restrictions
The INCLUDE
and EXCLUDE
parameters are mutually exclusive.
Grants on objects owned by the SYS
schema are never exported.
Example
The following example performs an export of all tables (and their dependent objects) in the hr
schema:
> expdp hr INCLUDE=TABLE DUMPFILE=dpump_dir1:exp_inc.dmp NOLOGFILE=YES