Oracle

Exporting tables from different Schemas using EXPDP

Posted Anand

Recently i saw a thread in OTN forum where it was asked “How to export (using EXPDP) tables from different schemas in a single expdp commad”.
Suppose you are asked to take export backup of SCOTT’s emp and HR’s jobs tables, how would you do it??
Using the traditional exp-imp method :-

D:\>exp file= exp_test.dmp log=exp_test.log tables='scott.emp','hr.jobs' feedback=10
Export: Release 10.2.0.4.0 - Production on Mon Oct 18 22:25:05 2010
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Username: sys as sysdba
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Current user changed to SCOTT
. . exporting table                            EMP
.
                                                           14 rows exported
Current user changed to HR
. . exporting table                           JOBS
.
                                                           19 rows exported
Export terminated successfully without warnings.

Sweet and Simple..isn’t it 😉
Now, lets try with EXPDP-IMPDP 

D:\>expdp directory=DATA_PUMP_DIR dumpfile=expdp_test_1.dmp logfile=expdp_test_1.log tables='scott.emp','hr.jobs'
Export: Release 10.2.0.4.0 - Production on Monday, 18 October, 2010 22:28:42
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Username: sys as sysdba
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
UDE-00012: table mode exports only allow objects from one schema
D:\>

Opps!!!! What’s that!! I didn’t knew this – “table mode exports only allow objects from one schema”.Does that mean we can’t take export of tables from different schemas in one command.Lets try something different

D:\>expdp directory=DATA_PUMP_DIR dumpfile=expdp_test_1.dmp logfile=expdp_test_1.log schemas='scott','hr' include=TABLE:\"IN (\'EMP\', \'JOBS\')\"
Export: Release 10.2.0.4.0 - Production on Monday, 18 October, 2010 22:30:06
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Username: sys as sysdba
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  sys/******** AS SYSDBA directory=DATA_PUMP_DIR dumpfile=expdp_test_1.dmp logfile=expdp_test_1.log schemas='scott','hr' include=TABLE:"IN (\'EMP\', \'JOBS\')"
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."JOBS"                                 6.609 KB      19 rows
. . exported "SCOTT"."EMP"                               7.820 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\PRODUCT\10.2.0\ADMIN\MATRIX\DPDUMP\EXPDP_TEST_1.DMP
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 22:30:19
D:\>

Hey this works 🙂 .Will the change in the sequence of the schema names or the table names make any difference.Will expdp throw some ORA error.Changing the schema names sequence

D:\>expdp directory=DATA_PUMP_DIR dumpfile=expdp_test_2.dmp logfile=expdp_test_2.log schemas='hr','scott' include=TABLE:\"IN (\'EMP\', \'JOBS\')\"
Export: Release 10.2.0.4.0 - Production on Monday, 18 October, 2010 22:33:19
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Username: sys as sysdba
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  sys/******** AS SYSDBA directory=DATA_PUMP_DIR dumpfile=expdp_test_2.dmp logfile=expdp_test_2.log schemas='hr','scott' include=TABLE:"IN (\'EMP\', \'JOBS\')"
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."JOBS"                                 6.609 KB      19 rows
. . exported "SCOTT"."EMP"                               7.820 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\PRODUCT\10.2.0\ADMIN\MATRIX\DPDUMP\EXPDP_TEST_2.DMP
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 22:33:34
D:\>

Changing the Table names sequence.

D:\>expdp directory=DATA_PUMP_DIR dumpfile=expdp_test_3.dmp logfile=expdp_test_3.log schemas='scott','hr' include=TABLE:\"IN (\'JOBS\', \'EMP\')\"
Export: Release 10.2.0.4.0 - Production on Monday, 18 October, 2010 22:35:18
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Username: sys as sysdba
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  sys/******** AS SYSDBA directory=DATA_PUMP_DIR dumpfile=expdp_test_3.dmp logfile=expdp_test_3.log schemas='scott','hr' include=TABLE:"IN (\'JOBS\', \'EMP\')"
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."JOBS"                                 6.609 KB      19 rows
. . exported "SCOTT"."EMP"                               7.820 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\PRODUCT\10.2.0\ADMIN\MATRIX\DPDUMP\EXPDP_TEST_3.DMP
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 22:35:32
D:\>

So isn’t EXPDP intelligent 🙂
You can trace the expdp using “trace=480300” in the expdp/impdp command.Few lines from the generated trace.

*** ACTION NAME:(SYS_EXPORT_SCHEMA_01) 2010-10-18 22:31:43.500
*** MODULE NAME:(Data Pump Master) 2010-10-18 22:31:43.500
*** SERVICE NAME:(SYS$USERS) 2010-10-18 22:31:43.500
*** SESSION ID:(136.9) 2010-10-18 22:31:43.500
KUPM: 22:31:43.718: *******IN DISPATCH at 81103, request type=1035
KUPM: 22:31:43.718: Current user is: SYS
KUPM: 22:31:43.718: DBMS_DATAPUMP.SET_PARAMETER (hand, 'COMMAND_LINE_CLIENT', 1);
KUPM: 22:31:43.718: *******OUT DISPATCH  , request type=1035   response type =2041
KUPM: 22:31:43.734: *******IN DISPATCH at 81103, request type=1035
KUPM: 22:31:43.734: Current user is: SYS
KUPM: 22:31:43.734: DBMS_DATAPUMP.SET_PARAMETER (hand, 'CLIENT_COMMAND', 'sys/******** AS SYSDBA directory=DATA_PUMP_DIR dumpfile=expdp2_test.dmp logfile=expdp2_test.log schemas='hr','scott' include=TABLE:"IN (\'EMP\', \'JOBS\')" trace=480300 ');
KUPM: 22:31:43.734: *******OUT DISPATCH  , request type=1035   response type =2041
KUPM: 22:31:43.750: *******IN DISPATCH at 81103, request type=1032
KUPM: 22:31:43.750: Current user is: SYS
KUPM: 22:31:43.750: In metadata filter....
KUPM: 22:31:43.750: DBMS_DATAPUMP.METADATA_FILTER (hand, 'SCHEMA_LIST', ''HR','SCOTT'', '');
KUPM: 22:31:43.750: In parse_list_filter...
KUPM: 22:31:43.750: Starting row for filter parse is: 1
KUPM: 22:31:43.750: Next filter element is HR
KUPM: 22:31:43.750: Element added to master at duplicate=1
KUPM: 22:31:43.750: Next filter element is SCOTT
KUPM: 22:31:43.750: Element added to master at duplicate=2
KUPM: 22:31:43.750: Expression filter will be: IN (SELECT object_name FROM "SYS"."SYS_EXPORT_SCHEMA_01" WHERE process_order = -55 AND duplicate BETWEEN 1 AND 2)
KUPM: 22:31:43.796: *******OUT DISPATCH  , request type=1032   response type =2041
KUPM: 22:31:43.812: *******IN DISPATCH at 81103, request type=1022
KUPM: 22:31:43.812: Current user is: SYS
KUPM: 22:31:43.812: DBMS_DATAPUMP.ADD_FILE (hand, 'expdp2_test.dmp', 'DATA_PUMP_DIR', 0, 1);
KUPM: 22:31:43.875: *******OUT DISPATCH  , request type=1022   response type =2041
KUPM: 22:31:43.875: *******IN DISPATCH at 81103, request type=1032
KUPM: 22:31:43.875: Current user is: SYS
KUPM: 22:31:43.875: In metadata filter....
KUPM: 22:31:43.875: DBMS_DATAPUMP.METADATA_FILTER (hand, 'NAME_EXPR', 'IN ('EMP', 'JOBS')', 'TABLE');
KUPM: 22:31:43.890: *******OUT DISPATCH  , request type=1032   response type =2041
KUPM: 22:31:43.890: *******IN DISPATCH at 81103, request type=1032
KUPM: 22:31:43.890: Current user is: SYS
KUPM: 22:31:43.890: In metadata filter....
KUPM: 22:31:43.890: DBMS_DATAPUMP.METADATA_FILTER (hand, 'INCLUDE_PATH_LIST', ''TABLE'', '');
KUPM: 22:31:43.890: In parse_list_filter...
KUPM: 22:31:43.890: Starting row for filter parse is: 3
KUPM: 22:31:43.890: Next filter element is TABLE
KUPM: 22:31:43.890: Element added to master at duplicate=3
KUPM: 22:31:43.890: Expression filter will be: IN (SELECT object_name FROM "SYS"."SYS_EXPORT_SCHEMA_01" WHERE process_order = -55 AND duplicate BETWEEN 3 AND 3)
KUPM: 22:31:43.906: *******OUT DISPATCH  , request type=1032   response type =2041
KUPM: 22:31:43.921: *******IN DISPATCH at 81103, request type=1003
KUPM: 22:31:43.921: Current user is: SYS
KUPM: 22:31:43.921: DBMS_DATAPUMP.START_JOB (hand, 0, 0);
KUPM: 22:31:43.921: ...defaulting parameter ESTIMATE
KUPM: 22:31:43.921: ...defaulting parameter DATA_ACCESS_METHOD
KUPM: 22:31:43.921: ...defaulting parameter INCLUDE_METADATA
KUPM: 22:31:43.921: ...defaulting parameter KEEP_MASTER
KUPM: 22:31:43.921: ...defaulting parameter MASTER_ONLY
KUPM: 22:31:43.921: ...defaulting parameter METRICS
KUPM: 22:31:43.921: ...defaulting parameter REUSE_DATAFILES
KUPM: 22:31:43.921: ...defaulting parameter SKIP_UNUSABLE_INDEXES
KUPM: 22:31:43.921: ...defaulting parameter STREAMS_CONFIGURATION
KUPM: 22:31:43.937: ...defaulting parameter TABLE_CONSISTENCY
KUPM: 22:31:43.937: ...defaulting parameter TABLE_EXISTS_ACTION
KUPM: 22:31:43.937: ...defaulting parameter TTS_FULL_CHECK
KUPM: 22:31:43.937: ...defaulting parameter USER_METADATA
KUPM: 22:31:44.187: Stmt to load DATAPUMP_PATHS is: INSERT INTO "SYS"."SYS_EXPORT_SCHEMA_01" (process_order, duplicate, object_path_seqno,   object_type_path, object_name) SELECT :1, ROWNUM, seq_num, full_path, object_path FROM DATAPUMP_PATHS WHERE HET_TYPE = :2
KUPM: 22:31:44.296: Stmt to load DATAPUMP_PATHMAP is: INSERT INTO "SYS"."SYS_EXPORT_SCHEMA_01" (process_order, duplicate, object_path_seqno,   object_name) SELECT :1, ROWNUM, seq_num, het_type FROM   (SELECT p.seq_num seq_num, m.het_type het_type    FROM DATAPUMP_
KUPM: 22:31:44.296: _PATHMAP m,         DATAPUMP_PATHS p    WHERE (m.object_path = p.full_path) AND          (p.object_path = p.full_path) AND          (p.het_type = :2))
KUPM: 22:31:44.453: In BUILD_MTABLE_INDEXES
KUPM: 22:31:44.531: Trying to create master table index: CREATE INDEX SYS_MTABLE_00000CF42_IND_1 ON "SYS"."SYS_EXPORT_SCHEMA_01" (object_schema, object_name, object_type)
KUPM: 22:31:44.546: Trying to create master table index: CREATE INDEX SYS_MTABLE_00000CF42_IND_2 ON "SYS"."SYS_EXPORT_SCHEMA_01" (base_process_order)
KUPM: 22:31:44.546: Indexes have been built.
KUPM: 22:31:44.546: Entered state: EXECUTING
KUPM: 22:31:44.562: Log message received from MCP
KUPM: 22:31:44.562: Starting "SYS"."SYS_EXPORT_SCHEMA_01":  sys/******** AS SYSDBA directory=DATA_PUMP_DIR dumpfile=expdp2_test.dmp logfile=expdp2_test.log schemas='hr','scott' include=TABLE:"IN (\'EMP\', \'JOBS\')" trace=480300
KUPM: 22:31:44.609: Starting worker: 1 using SYS.KUPW$WORKER.MAIN('SYS_EXPORT_SCHEMA_01', 'SYS');
KUPM: 22:31:44.625: Worker started.
KUPM: 22:31:44.625: *******OUT DISPATCH  , request type=1003   response type =2041
KUPM: 22:31:45.203: *******IN DISPATCH at 81105, request type=3001
KUPM: 22:31:45.218: Current user is: SYS
KUPM: 22:31:45.218: GET_WORK from worker 1

*** ACTION NAME:(SYS_EXPORT_SCHEMA_01) 2010-10-18 22:31:45.078
*** MODULE NAME:(Data Pump Worker) 2010-10-18 22:31:45.078
*** SERVICE NAME:(SYS$USERS) 2010-10-18 22:31:45.078
*** SESSION ID:(135.12) 2010-10-18 22:31:45.078
KUPW: 22:31:45.187: 1: Master table is          :  "SYS"."SYS_EXPORT_SCHEMA_01"
KUPW: 22:31:45.187: 1: Metadata job mode is     : SCHEMA_EXPORT
KUPW: 22:31:45.187: 1: Debug enable is          : TRUE
KUPW: 22:31:45.187: 1: Timestamp enable is      : TRUE
KUPW: 22:31:45.187: 1: Profile enable is        : FALSE
KUPW: 22:31:45.187: 1: db version               : 10.2.0.3.0
KUPW: 22:31:45.187: 1: Abort Step               : 0
KUPW: 22:31:45.187: 1: Access Method            : AUTOMATIC
KUPW: 22:31:45.187: 1: Estimate                 : BLOCKS
KUPW: 22:31:45.187: 1: Remote Link              :
KUPW: 22:31:45.187: 1: Dumpfile present         : TRUE
KUPW: 22:31:45.187: 1: Table Exists Action      :
KUPW: 22:31:45.187: 1: Tablespace Datafile Count: 0
KUPW: 22:31:45.187: 1: Metadata Filter Count    : 6
KUPW: 22:31:45.187: 1: Metadata Filter Name     - INCLUDE_USER
KUPW: 22:31:45.187: 1:                    Value - TRUE
KUPW: 22:31:45.187: 1:                   Object -
KUPW: 22:31:45.187: 1: Metadata Filter Name     - SCHEMA_EXPR
KUPW: 22:31:45.187: 1:                    Value - IN (SELECT object_name FROM "SYS"."SYS_EXPORT_SCHEMA_01" WHERE process_order = -55 AND duplicate BETWEEN 1 AND 2)
KUPW: 22:31:45.187: 1:                   Object -
KUPW: 22:31:45.187: 1: Metadata Filter Name     - NAME_EXPR
KUPW: 22:31:45.187: 1:                    Value - IN ('EMP', 'JOBS')
KUPW: 22:31:45.187: 1:                   Object - TABLE
KUPW: 22:31:45.187: 1: Metadata Filter Name     - INCLUDE_PATH_EXPR
KUPW: 22:31:45.187: 1:                    Value - IN (SELECT object_name FROM "SYS"."SYS_EXPORT_SCHEMA_01" WHERE process_order = -55 AND duplicate BETWEEN 3 AND 3)
KUPW: 22:31:45.187: 1:                   Object -
KUPW: 22:31:45.187: 1: Metadata Filter Name     - ORDERED
KUPW: 22:31:45.187: 1:                    Value - FALSE
KUPW: 22:31:45.187: 1:                   Object - TABLE_DATA
KUPW: 22:31:45.187: 1: Metadata Filter Name     - PRIVILEGED_USER
KUPW: 22:31:45.187: 1:                    Value - TRUE
KUPW: 22:31:45.187: 1:                   Object -
KUPW: 22:31:45.187: 1: MD remap schema Count    : 0
KUPW: 22:31:45.187: 1: MD Transform remap Count : 0
KUPW: 22:31:45.203: 1: MD Transform ddl Count   : 0
KUPW: 22:31:45.203: 1: Data Filter Count        : 0

Something i learned and thought of sharing 🙂

10 thoughts on “Exporting tables from different Schemas using EXPDP”

  1. How to export 10g table to 11g database where in 11g table columns have been splited into 2 more coulmns
    for example column x in table a in 10g is splited in to column x y z in table b in 11g

    Reply
  2. Pingback: Exporting tables from different Schemas using EXPDP – Revisited – version 11gR2 « Anand's Blog

  3. One question…if Schema_A and Schema_B both contain the same table, how can you instruct DataPump to only pull a certain table from Schema_A?
    For example, from Schema_A , we want TABLE_1 and TABLE_2. From Schema_B, we want TABLE_3. *However* Schema_B also contains a table named TABLE_2.
    If my PARFILE contains
    …
    schemas=SCHEMA_A, SCHEMA_B
    INCLUDE=TABLE:”IN( TABLE_1, TABLE_2, TABLE_3)”
    …
    Then when my export is run, I will get SCHEMA_A.TABLE_2 and SCHEMA_B.TABLE_2.
    How can I avoid this and, for example, only get SCHEMA_A.TABLE_2 in my export?

    Reply

    1. Hi Joe,
      First of all sorry for late reply. The scenario you are talking about is not possible for Release: 10.1 to 11.1.Bug 6860716: EXPORT&FILTER TABLES FROM MULTIPLE SCHEMAS IN DATA PUMP. The only way i think of is using EXP only.
      Regards,
      Anand

      Reply

Leave a Reply