Data Pump EXPDP IMPDP EXCLUDE and INCLUDE Options Features and examples

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.

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.

Data Pump Reference
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

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

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

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

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
  2*
SQL> /

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------
PROCEMP1                       PROCEDURE
TRIGEMP                        TRIGGER
SEQSCOTT                       SEQUENCE
DEPT                           TABLE
EXAMPLE                        TABLE PARTITION
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.

ORA-39001: invalid argument value
ORA-39071: Value for INCLUDE is badly formed.
ORA-00936: missing expression

ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00904: “DEPT”: invalid identifier

ORA-39001: invalid argument value
ORA-39041: Filter “INCLUDE” either identifies all object types or no object types.

ORA-39001: invalid argument value
ORA-39041: Filter “EXCLUDE” either identifies all object types or no object types

ORA-39001: invalid argument value
ORA-39038: Object path “USER” is not supported for TABLE jobs.

UDE-00011: parameter include is incompatible with parameter exclude

ksh: syntax error: ‘(’ unexpected

ORA-31655: no data or metadata objects selected for job

DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google