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”
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
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?
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
Nice tutorial. Didn’t expect these much difference that data pump can handle..
I think the data pump trace is a little hard to read…
Good one mate thanks for sharing
Thanks a lot mate 🙂
Anand
Hi Anand,
Some thing inturn I learned from the trace file …!!
Thanks buddy.
See more details on
http://shonythomas.blogspot.in/2012/02/datapump-vs-expimp-difference-or.html