Include or exclude specific objects while import/export using datapump utility

Oracle Data pump utility has lots of functionality. One of the major functionality is we can select or deselect one or more objects while export/import using data pump.

We can do this by passing data pump parameter INCLUDE or EXCLUDE
Let us check the following demonstration ……


EX: 1) We will use a parameter file here to pass arguments to data pump export utility:
Parameter file: example.par

DIRECTORY = dexp
DUMPFILE = example.dmp
LOGFILE = example.log
SCHEMAS = HR
INCLUDE = TABLE:"IN ('EMPLOYEES', 'DEPARTMENTS')"

Now we will initiate the data pump utility and use the parameter file to pass arguments
c:\ershad> expdp system/a parfile=example.par

Explanation: I am going to export only employees and departments table of HR schema. Rest of the tables are excluded

EX: 2) Parameter file: example.par

DIRECTORY = dexp
DUMPFILE = example.dmp
LOGFILE = example.log
SCHEMAS = HR
EXCLUDE = TABLE:"IN ('EMPLOYEES', 'DEPARTMENTS')"

c:\ershad> expdp system/a parfile=example.par

Explanation: I am going to export all the objects except employees and departments table of HR schema

ex: 3) we can directly use the same without the parameter file but in that case, we need to be a bit careful about the escape special characters .

C:\ershad>expdp system/a DIRECTORY=dexp DUMPFILE=example.dmp LOGFILE=example.log SCHEMAS=HR INCLUDE=TABLE:\"IN \(\'EMPLOYEES\', \'DEPARTMENTS\'\)\"

Look here , we have used \ to escape “ or ( or ‘

Incorrect escaping can result in errors such as: ksh: syntax error: '(' unexpected.

Note: a) Is it possible to use exclude and include parameter in the same job?
Answer is you can’t
b) All the dependent objects will also be included or excluded as you have used. For example , if you exclude a table , then indexes, constraints, grants, and triggers upon the table will also be excluded by the filter



QUICK TIPS:

EXCLUDE=INDEX:"= 'INDX_1'"
[‘=’ means only that index, ‘IN’ means you can mention multiple names]
INCLUDE=INDEX:"LIKE 'INDEX_%'"
[same as “like” operator in sqlplus]
EXCLUDE = TRIGGER:"IN ('T1', 'T2')", INDEX:"= 'INDX_1'", REF_CONSTRAINT
[multiple objects type together]
EXCLUDE=SCHEMA:"='HR'"
[excluding a complete schema or in other words, all objects of a schema]
EXCLUDE=USER:"= 'HR'"
[required if in the imported database user HR already exists and you want only the objects to be imported. so, here in export 'create user hr ' ddl will not be exported]


Thanks ,
have a nice day ...........

Do not wait to strike till the iron is hot; but make it hot by striking.
William B. Sprague