How to Exclude or Include Particular Object from Database Export: expdp

<<Back to Oracle DATAPUMP Main Page

Excluding Objects while exporting database

You can exclude objects while exporting database using EXCLUDE parameter.
Syntax and Description
EXCLUDE=object_type[:name_clause] [, ...]
The object_type specifies the type of object to be excluded.
NOTE: 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.
Run an export using expdp with par file as below.
$expdp system@PDB01 parfile=exp_full_pdb01.par

Excluding Particular Schema From Full Database Export

$ cat exp_full_pdb01.par
DIRECTORY=DUMP
DUMPFILE=EXP_PDB01_FULL%U.DMP
LOGFILE=EXP_PDB01_FULL.LOG
FULL=Y
EXCLUDE=SCHEMA:"='TEST'"
REUSE_DUMPFILES=Y

Excluding Multiple Schemas From Full Database Export

$ cat exp_full_pdb01.par
DIRECTORY=DUMP
DUMPFILE=EXP_PDB01_FULL%U.DMP
LOGFILE=EXP_PDB01_FULL.LOG
FULL=Y
EXCLUDE=SCHEMA:" IN ('TEST','SYS')"
REUSE_DUMPFILES=Y

Excluding Multiple SCHEMAS Starting Matching a Pattern

$ cat exp_full_pdb01.par
DIRECTORY=DUMP
DUMPFILE=EXP_PDB01_FULL%U.DMP
LOGFILE=EXP_PDB01_FULL.LOG
FULL=Y
EXCLUDE=SCHEMA:"LIKE 'TEST%'"
REUSE_DUMPFILES=Y

Excluding Index from Full Database Export

$ cat exp_full_pdb01.par
DIRECTORY=DUMP
DUMPFILE=EXP_PDB01_FULL%U.DMP
LOGFILE=EXP_PDB01_FULL.LOG
FULL=Y
EXCLUDE=INDEX:"LIKE 'INDX%'"
REUSE_DUMPFILES=Y

Excluding Few Tables from Full Database Export

$ cat exp_full_pdb01.par
DIRECTORY=DUMP
DUMPFILE=EXP_PDB01_FULL%U.DMP
LOGFILE=EXP_PDB01_FULL.LOG
FULL=Y
EXCLUDE=TABLE:"IN(select table_name from dba_tables where table_name in ('EMP','PEOPLE','DEPT'))"
REUSE_DUMPFILES=Y

EXPDP INCLUDE

The EXCLUDE and INCLUDE parameters are mutually exclusive and therefore you can not use them together. Only object types explicitly specified in INCLUDE statements, and their dependent objects, are exported.
An Example of using INCLUDE
$ cat exp_full_pdb01.par
DIRECTORY=DUMP
DUMPFILE=EXP_PDB01_FULL%U.DMP
LOGFILE=EXP_PDB01_FULL.LOG
SCHEMAS=TEST
INCLUDE=TABLE
REUSE_DUMPFILES=Y
If this parameter file is used for exporting SCHEMAS=TEST it will only export the tables from TEST schema and will ignore the rest.

$ cat exp_full_pdb01.par
DIRECTORY=DUMP
DUMPFILE=EXP_PDB01_FULL%U.DMP
LOGFILE=EXP_PDB01_FULL.LOG
SCHEMAS=TEST
INCLUDE=INDEX
REUSE_DUMPFILES=Y
If this parameter file is used for exporting SCHEMAS=TEST it will only export the indexes from TEST schema and will ignore the rest.

How to find the expdp EXCLUDE/INCLUDE supported object types

You can query OBJECT_PATH column from below tables to find the valid object_type which can be excluded/included while performing an export.
DATABASE_EXPORT_OBJECTS <= while exporting full database
SCHEMA_EXPORT_OBJECTS <= while exporting SCHEMA
TABLE_EXPORT_OBJECTS <=  while exporting TABLES