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
Prahlad
Very nice article boss….
Pawan Kumar Yaddanapudi
Cheers !!!
zddy
hi ..
how can i used parallel clause in datapump when my job is running, and i want to increase my worker without any error i had tried expdp console but its throwing so many errors . what is the correct way to add parallel when my job has been already executed .
vijay
Hi Sir,
Could you please help with the below error, getting this error during import.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_IMPORT_FULL_01 for user CO24743
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.KUPV$FT_INT”, line 810
ORA-39080: failed to create queues “KUPC$C_2_20180929232809” and “KUPC$S_2_20180929232809” for Data Pump job
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.KUPC$QUE_INT”, line 1539
ORA-20001: You can not CREATE VIEW in SYS schema.
vijay
there are no invalid objects in SYS schema.