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
