Data Pump EXPDP IMPDP EXCLUDE and INCLUDE Options Features and examples

advertisements

_____________________________________________________________________________________________________________________

Data Pump provides fine filtering of objects during the export or import through this exclude and include feature. We can use these exclude and include options with both the EXPDP and IMPDP utilities. It is kind of object exception marking during the expdp or impdp.
If you use exclude parameter with data pump, all the objects except the objects mentioned in the EXCLUDE will be considered for the operation. I feel like it is very good feature with data pump. EXCLUDE and INCLUDE is applicable for the database objects like tables, indexes, triggers, procedures etc.. In the traditional exp/imp utility we have different options for different objects in the database and that too limited to some certain objects like table=<list of tables> indexes=N etc.. In data pump it is more flexible as you can include multiple objects with multiple clauses. See below the examples. J

Table partitions are the exception for the EXCLUDE option in data pump. See below mentioned link.

Datapump Exclude Table

Syntax

INCLUDE=object_type[:name_clause] [,object_type[:name_clause]]

EXCLUDE=object_type[:name_clause] [,object_type[:name_clause]]

In the name_clause you can use the expressions with operators like IN, NOT IN, LIKE, =, and so on.. to filter the objects according to your requirement.

Only limitation or disadvantage I could see is it cannot use with SCHEMAS option with the datapump. See the error message if you try exclude option for schema operations.

$ expdp exclude=INDEXES:"LIKE 'SCOTT.EXAM%'" directory=exp_dir dumpfile=scott.dmp logfile=exp_scott.log

Export: Release 11.1.0.7.0 - 64bit Production on Tuesday, 28 June, 2011 0:05:03

Username: / AS SYSDBA

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-39001: invalid argument value

ORA-39038: Object path "INDEXES" is not supported for SCHEMA jobs.

$ expdp exclude=TABLES:"LIKE 'SCOTT.EXAM%'" directory=exp_dir dumpfile=scott.dmp logfile=exp_scott.log

Export: Release 11.1.0.7.0 - 64bit Production on Tuesday, 28 June, 2011 0:02:55

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-39001: invalid argument value

ORA-39038: Object path "TABLES" is not supported for SCHEMA jobs.

Examples:

Please find the list objects in the scott schema for example scenarios.

1 select object_name, object_type from user_objects

SQL> /

OBJECT_NAME OBJECT_TYPE

------------------------------ ------------------------------

PROCEMP1 PROCEDURE

TRIGEMP TRIGGER

SEQSCOTT SEQUENCE

DEPT TABLE

EXAMPLE TABLE PARTITION

EXAMPLE TABLE

SYS_C00278761 INDEX

EMP1 TABLE

EXAMPLE_PARTITION TABLE

TMP$$_SYS_C002787610 INDEX

GT_EMP TABLE

12 rows selected.

Example#1. Command to exclude tables name starts with EXAM

expdp exclude=TABLE:"LIKE 'EXAM%'" directory=exp_dir dumpfile=scott.dmp logfile=exp_scott.log

Example#2. Command to exclude tables EMP1 and DEPT

expdp exclude=TABLE:"IN ('EMP1','DEPT')" directory=exp_dir dumpfile=scott.dmp logfile=exp_scott.log

Example#3. Command to Exclude all sequence, procedures, indexes and tables EMP1 AND DEPT

expdp exclude=SEQUENCE,PROCEDURE,INDEXE,TABLE:"IN ('EMP1','DEPT')" directory=exp_dir dumpfile=scott.dmp logfile=exp_scott.log

Example#4 Command to include all the tables starts above the alphabet ‘F’

expdp exclude=TABLES:">'F'" directory=exp_dir dumpfile=scott.dmp logfile=exp_scott.log

EXCLUDE or INCLUDE parameters can be used in the parameter file for the data pump.

See the Sample parameter file below.

exclude=SEQUENCE,PROCEDURES,INDEXES,TABLES:"IN ('EMP1','DEPT')"

directory=exp_dir

dumpfile=scott.dmp

logfile=exp_scott.log

Parallel=4

Userid=/ as sysdba

Below mentioned are the error messages related to the exclude include.