loading
hello dummmy text

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

5 Comment

  1. Prahlad

    Very nice article boss….

    1. Pawan Kumar Yaddanapudi

      Cheers !!!

  2. 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 .

  3. 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.

    1. vijay

      there are no invalid objects in SYS schema.

Write a Reply or Comment

Your email address will not be published. Required fields are marked *

Knowledge That Can Always Keep Your Inbox Informed