6 Little Known Use of Expdp Exclude and Expdp Include Parameter

Expdp Exclude and Expdp Include are very handy parameters of Expdp when DBA needs backup of specific objects from database. Exclude parameter with Expdp is used to avoid backup of specific objects listed in Exclude parameters, While Include parameter with Expdp is used to only take backups of objects listed in Included parameter. The EXCLUDE and INCLUDE parameters are mutually exclusive to each other. 

Even after being very helpful tool, I see very few DBA's are using these parameters frequently, Hence, I decide to write a post about these precious options.

Suppose Database Administrator want's to take a schema backup excluding only one larger table, In that case He has to provide table name as input to Expdp Exclude parameter. 

In contrary to that, IF DBA want's to take backup of only one table from schema then He has to use Include parameter with expdp command. This will take backup of only given table.

Expdp command can have either exclude or include parameter at one time, but you can have multiple exclude or include parameter with single expdp command. In this post, I will explain use of both exclude and include parameter with Expdp. I assume, you know about all basic expdp parameters like directory, filename, logfile etc or you can find here.


3 Use of Expdp Exclude Parameter


Let's start with exclude. There are three  ways in which you can use exclude with expdp parameter.


1. Exclude Schema when exporting full database.
2. Excluding type of objects when exporting single schema.
3. Excluding individual objects when exporting single schema.


1. Exclude Schema when exporting full database: Suppose DBA want's to export whole database except one or more schema, In that case he can use exclude parameter to achieve this.

Command:
[oracle@database OPatch]$ expdp system/pwd directory=data_pump_dir dumpfile=full_database.dmp full=yes logfile=full_database.log exclude=SCHEMA:"in\('SCOTT'\)"

The syntax for Expdp exclude is not given correctly at Oracle documentation. If you will follow Oracle documentation, you will see below error message.

[oracle@database OPatch]$ expdp system/sys directory=data_pump_dir dumpfile=full_database.dmp full=yes logfile=full_database.log exclude=SCHEMA:"='SCOTT'"

LRM-00116: syntax error at 'SCHEMA:' following '='

The correct syntax for expdp exclude schema is exclude=SCHEMA:"in\('SCHEMA_NAME'\)"

If you wants to Exclude more than one schema use below syntax and example

Syntax: exclude=SCHEMA:"in\('schema_name'\,'schema_name'\)"

Example:

[oracle@database OPatch]$ expdp system/sys directory=data_pump_dir dumpfile=full_database.dmp full=yes logfile=full_database.log exclude=SCHEMA:"in\('SCOTT'\,'HR'\)"

In this way, DBA can export only one or more than one schema. Though this can also be achieved without full=yes parameter. Database Administrator can know more about Full database Export and import at What Everybody Should Know about Export Database and Import Database In Oracle ?


2. Excluding a type of objects when exporting single schema: Suppose, Database Administrator find that, there was no change in table data but other objects ware changed on prod and you want your production data to restore in test environment without tables. So, if you can avoid table backup from expdp, this will achieve your goal. In that case Database Administrator can use. "expdp=table" option.

[oracle@database OPatch]$ expdp scott/tiger directory=data_pump_dir dumpfile=full_database.dmp  logfile=full_database.log exclude=table 
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** directory=data_pump_dir dumpfile=full_database.dmp logfile=full_database.log exclude=table 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  /etc/oracle/admin/orcl/dpdump/full_database.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 22:50:46


Here, you can notice in export output no table is exported to the dump file. DBA can also exclude multiple type of objects with more than one exclude parameters like below.

[oracle@database OPatch]$ expdp scott/tiger directory=data_pump_dir dumpfile=full_database.dmp  logfile=full_database.log exclude=table exclude=procedure 
Export: Release 11.2.0.2.0 - Production on Thu Jul 4 22:58:21 2013
Starting "SCOTT"."SYS_EXPORT_SCHEMA_02":  scott/******** directory=data_pump_dir dumpfile=full_database.dmp logfile=full_database.log exclude=table exclude=procedure 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Master table "SCOTT"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_02 is:
/etc/oracle/admin/orcl/dpdump/full_database.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_02" successfully completed at 22:58:28

In above output, you can see, both table and procedures are excluded from export command. Exclude command with object type option is very helpful for reducing dump size and saving time for database export. You can know more about How to Take schema backup using Expdp ?  and How to Restore schema using Impdp ?


3. Excluding individual objects when exporting single schema: Let's take another case, you have 100 tables in your schema and you want to export only 99 of them except two which are huge in size and already available at destination. Here, expdp exclude=table:"in\('EMP'\,'DEPT'\)" parameter is best solution for DBA. Make sure you provide table name in upper case, since values given are case sensitive.

[oracle@database OPatch]$ expdp scott/tiger directory=data_pump_dir dumpfile=full_database.dmp  logfile=full_database.log exclude=table:"in\('EMP'\,'DEPT'\)"

With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_02":  scott/******** directory=data_pump_dir dumpfile=full_database.dmp logfile=full_database.log exclude=table:in\('EMP'\,'DEPT'\) 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 39.31 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
-------------------------------------------------
-------------------------------------------------
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."PEOPLE"                            32.19 MB 1000000 rows
. . exported "SCOTT"."SYS_EXPORT_SCHEMA_01"              133.8 KB    1114 rows
. . exported "SCOTT"."SALGRADE"                          5.867 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_02 is:
/etc/oracle/admin/orcl/dpdump/full_database.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_02" successfully completed at 23:15:23


When you exclude a object it's dependent objects are also excluded from backup. like excluding of table also exclude triggers and indexed created on the table. DBA can have multiple exclude parameter and each exclude parameter excluding multiple objects.
[oracle@database OPatch]$ expdp scott/tiger directory=data_pump_dir dumpfile=full_database.dmp  logfile=full_database.log exclude=table:"in\('EMP'\,'DEPT'\)" exclude=procedure:"in\('TEST_PROC'\)"

Here, we are done with Expdp Exclude options, Now let's see how Expdp include works.


3 Use of Expdp Include Parameter


Again, There are three main ways in which you can use include with expdp parameter.


1. Include Schema when exporting full database.
2. Including type of objects when exporting single schema.
3. Including individual objects when exporting single schema.


1. Include Schema when exporting full database: When taking full database backup, DBA can choose to take only backup of selected schema, though this option is same as taking database backup with system user and adding schema name as the parameter. Since, this is an available option so I am discussing it here.

[oracle@database]$ expdp system/pwd directory=data_pump_dir dumpfile=full_database.dmp full=yes logfile=full_database.log INCLUDE=SCHEMA:"in\('SCOTT'\)"

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
---------------------------------------------------------------
---------------------------------------------------------------
Master table "SYSTEM"."SYS_EXPORT_FULL_03" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_03 is:
/etc/oracle/admin/orcl/dpdump/full_database.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_03" successfully completed at 23:28:56

I don't find so much use of this option in day to day DBA life.


2. Including type of objects when exporting single schema: Suppose Development has done changes into 100 schema procedures, So Now, DBA has to more these procedures to production database. One options is to note all of them at one plance then take backup and then restore at prod env. another option is to take backup of all schema procedures and restore on production environment.  Here, Expdp with include option is one of the good way to do this.

[oracle@database OPatch]$ expdp scott/tiger directory=data_pump_dir dumpfile=full_database.dmp  logfile=full_database.log include=procedure

Export: Release 11.2.0.2.0 - Production on Thu Jul 4 23:49:21 2013
Starting "SCOTT"."SYS_EXPORT_SCHEMA_03":  scott/******** directory=data_pump_dir dumpfile=full_database.dmp logfile=full_database.log include=procedure 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Master table "SCOTT"."SYS_EXPORT_SCHEMA_03" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_03 is:
 /etc/oracle/admin/orcl/dpdump/full_database.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_03" successfully completed at 23:49:24


DBA can also use multiple include parameter same way as in exclude parameter.

[oracle@database OPatch]$  expdp scott/tiger directory=data_pump_dir dumpfile=full_database.dmp  logfile=full_database.log include=procedure include=table


3. Including individual objects when exporting single schema: Suppose DBA wants to export only two tables from production to test environment in that case expdp include with parameter details will help. Again, You can import multiple type of objects and further selected objects out of total objects.

[oracle@database OPatch]$ expdp scott/tiger directory=data_pump_dir dumpfile=full_database.dmp  logfile=full_database.log include=table:"in\('EMP'\,'DEPT'\)"

Export: Release 11.2.0.2.0 - Production on Fri Jul 5 04:19:03 2013
Starting "SCOTT"."SYS_EXPORT_SCHEMA_03":  scott/******** directory=data_pump_dir dumpfile=full_database.dmp logfile=full_database.log include=table:in\('EMP'\,'DEPT'\) 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.953 KB       5 rows
. . exported "SCOTT"."EMP"                               8.570 KB      14 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_03" successfully loaded/unloaded
*****************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_03 is:
/etc/oracle/admin/orcl/dpdump/full_database.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_03" successfully completed at 04:19:42


From the above example, you can see only two tables EMP and DEPT are exported. To export multiple type objects use below command

[oracle@database OPatch]$ expdp scott/tiger directory=data_pump_dir dumpfile=full_database.dmp  logfile=full_database.log include=table:"in\('EMP'\,'DEPT'\)" include=procedure:"in\('TEST_PROC'\)"


One thing to make sure, Provide object name in upper case otherwise command will execute successfully but you will not get desired results. These commands are very helpful in moving data from one schema to another schema and will same DBA time and resources during export, import activities.

Please share your feedback about this post !

3 comments:

  1. It is informative and good post.
    I found lot of data pump reference examples here.
    http://www.acehints.com/p/datapump.html

    ReplyDelete
  2. Good examples.

    Thanks,
    Charlie 木匠 | Database Architect Developer

    ReplyDelete
  3. Highly useful and very nice information. Thank you. Most of the examples above didn't mention FULL=Y or SCHEMAS=SCOTT options. Is it typo? or don't we need to specify these at all in this case? Please suggest.

    For ex.,

    expdp scott/tiger directory=data_pump_dir dumpfile=full_database.dmp logfile=full_database.log include=table:"in\('EMP'\,'DEPT'\)" include=procedure:"in\('TEST_PROC'\)"

    In above there is no parameter for full/schema.

    ReplyDelete