Exporting tables from different Schemas using EXPDP – Revisited – version 11gR2

More than an year ago i had written http://aprakash.wordpress.com/2010/10/19/exporting-tables-from-different-schemas-using-expdp/ where in mentioned how to export tables from different schemas using the EXPDP in 10gR2.

Using exp –

"D:\>exp file= exp_test.dmp log=exp_test.log tables='scott.emp','hr.jobs' feedback=10"

Using expdp

D:\>expdp directory=DATA_PUMP_DIR dumpfile=expdp_test_1.dmp logfile=expdp_test_1.log schemas='scott','hr' include=TABLE:\"IN (\'EMP\', \'JOBS\')\"

With the above expdp command the problem arises when tables with same name are in different schemas.For example, suppose i have EMP table both in SCOTT and ANAND schema, but i want to export DEPT table of scott and EMP of ANAND, it isn’t possible using EXPDP.

D:\scripts>expdp directory=DATA_PUMP_DIR dumpfile=expdp_test_1.dmp logfile=expdp_test_1.log schemas='scott','anand' include=TABLE:\"IN (\'EMP\', \'DEPT\')\"

Export: Release 11.2.0.2.0 - Production on Sun Feb 12 18:44:26 2012

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA directory=DATA_PUMP_DIR dumpfile=expdp_test_1.dmp logfile=expdp_test_1.log schemas='scott','anand' include=TABLE:"IN (\'EMP\', \'DEPT\')"
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 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/AUDIT_OBJ
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ANAND"."EMP"                               8.562 KB      14 rows
. . exported "SCOTT"."DEPT"                              5.929 KB       4 rows
. . exported "SCOTT"."EMP"                               8.562 KB      14 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  D:\ORACLE\ADMIN\MATRIX\DPDUMP\EXPDP_TEST_1.DMP
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 18:44:59

EMP table for both ANAND and SCOTT got exported. The only way to do this is using exp command.It is filed as BUG (Bug 6860716: EXPORT&FILTER TABLES FROM MULTIPLE SCHEMAS IN DATA PUMP) for release 10.1 to 11.1

From 11gR2, Oracle EXPDP included the feature TABLES=[schema_name.]table_name[:partition_name] [, ...], similar to what EXP has, which resolves this issue.

D:\scripts>expdp directory=DATA_PUMP_DIR dumpfile=expdp_test_1.dmp logfile=expdp_test_1.log tables=anand.emp,scott.dept  REUSE_DUMPFILES=Y

Export: Release 11.2.0.2.0 - Production on Sun Feb 12 18:50:45 2012

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA directory=DATA_PUMP_DIR dumpfile=expdp_test_1.dmp logfile=expdp_test_1.log tables=anand.emp,scott.dept REUSE_DUMPFILES=Y
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ANAND"."EMP"                               8.562 KB      14 rows
. . exported "SCOTT"."DEPT"                              5.929 KB       4 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  D:\ORACLE\ADMIN\MATRIX\DPDUMP\EXPDP_TEST_1.DMP
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 18:50:58
About these ads
    • Pepa
    • March 19th, 2013

    Hi, is there any way how to do this (to export tables with same name only from specific schema – TABLES=schema_name.]table_name[:partition_name]) with DBMS_DATAPUMP interface?
    Thank you.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 463 other followers

%d bloggers like this: