INCLUDE & EXCLUDE database object’s with Data Pump Export Utility

Most of the time we need to backup single database object instead of full schema & database, in this scenario we can use data pump parameter INCLUDE/EXCLUDE to export or import objects as per our convenience.
These parameters are used to limit the export/import to specific objects.

INCLUDE: Data Pump parameter to be specify objects to be included while export/import.
EXCLUDE: Data Pump parameter to be specify objects to be exclude while export/import.

Syntax:
INCLUDE = object_type [:name_clause][, ..]
EXCLUDE = object_type [:name_clause][, ..]

Note:
We can’t use both the parameter at a time. both are the mutually exclusive of each other.

Please consider following hand-on on INCLUDE/EXCLUDE:

Consider full database export with only tables & views to be exported with the help of include parameter:
expdp system/manager directory=data_pump_bkup dumpfile=full_include.dmp logfile=full_include.log full=y include=table,view

 

Consider full database export, in this all objects to be exported instead of procedures & packages with the help of exclude parameter:
expdp system/manager directory=data_pump_bkup dumpfile=full_exclude.dmp logfile=full_exclude.log full=y exclude=procedure,package

 

Note:
We can use multiple INCLUDE/EXCLUDE parameter’s within one export/import command.

Following both the expressions are elligible:
expdp system/manager … INCLUDE=table,procedure,view

OR

expdp system/manager … INCLUDE=table INCLUDE=procedure INCLUDE=view

For more details about expdp/impdp status you can query views DATABASE_EXPORT_OBJECTS, SCHEMA_EXPORT_OBJECTS, and TABLE_EXPORT_OBJECTS.

************************************************************************
Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Leave a Reply