EXPDP backup fails – EXCLUDE and INCLUDE options

I have set of tables to be excluded with the name AA_* but to include one table AA_SYNTAX in the datapump export on Oracle 11g R2 database. I have found something very strange when I tried using the following syntax with EXPDP.

I have executed few test cases to check which one will work for my requirement.

Test 1: Just to check if multiple entries in EXCLUDE works, I have tested that in INCLUDE parameter.

Syntax:

expdp dumpfile=auto_schema_TEST.dmp directory=DIREC logfile=auto_schema_TEST.log INCLUDE=TABLE:\”LIKE \’AA_%\’\”,TABLE:\”NOT LIKE \’AA_SYNTAX\’\” schemas=ABCD

 

Result:

Export: Release 11.2.0.3.0 – Production on Wed Jun 3 10:27:04 2015

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: system

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options FLASHBACK automatically enabled to preserve database integrity.

Starting “SYSTEM”.”SYS_EXPORT_SCHEMA_03″:  system/***Estimate in progress using BLOCKS method…

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 384 KB

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported “ABCD”.”AA_00_1″                 48.54 KB      36 rows

. . exported “ABCD”.”AA_00_2″                 44.99 KB     412 rows

Master table “SYSTEM”.”SYS_EXPORT_SCHEMA_03″ successfully loaded/unloaded

****************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_03 is:

/Datapump/auto_schema_TEST.dmp

Job “SYSTEM”.”SYS_EXPORT_SCHEMA_03″ successfully completed at 10:27:36

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_03 is:
/Datapump/auto_schema_TEST.dmp
Job “SYSTEM”.”SYS_EXPORT_SCHEMA_03″ successfully completed at 10:27:36

 

Test 2: The same syntax doesn’t work in EXCLUDE clause. Please NOTE – EXPDP doesnt throw any error, But job is not performed as expected. Syntax:

expdp dumpfile=auto_schema_TEST1.dmp directory=DIREC logfile=auto_schema_TEST.log EXCLUDE=TABLE:\”LIKE \’AA_%\’\”,TABLE:\”NOT LIKE \’AA_SYNTAX\’\” schemas=ABCD

 

Result:

Export: Release 11.2.0.3.0 – Production on Wed Jun 3 10:30:41 2015

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

Username: system

Password:

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options FLASHBACK automatically enabled to preserve database integrity.

Estimate in progress using BLOCKS method…

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/SEQUENCE/SEQUENCE

Master table “SYSTEM”.”SYS_EXPORT_SCHEMA_03″ successfully loaded/unloaded

****************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_03 is:

/aux/data/transport/auto_schema_TEST1.dmp

Job “SYSTEM”.”SYS_EXPORT_SCHEMA_03″ successfully completed at 10:31:19

I have identified from these test cases that the way I can get rid of this issue is by using SELECT query in the EXCLUDE clause.

Solution:

expdp system dumpfile=auto_schema_ABCD_%U.dmp directory=DIREC logfile=auto_schema_ABCD.log

EXCLUDE=TABLE:\”IN\(select table_name from dba_tables where owner=\’ABCD\’ and table_name like \’AA_\%\’ and table_name \!= \’AA_SYNTAX\’ \)\”

schemas=ABCD parallel=4 compression=all

 

About The Author

2 Comments

  1. prahlad.chowdary@gmail.com'
    Prahlad
    February 9, 2017
    • Pawan Kumar Yaddanapudi
      February 9, 2017

Add Comment