How to export tables from multiple schemas with Oracle Data Pump in Oracle 10g and 11g databases
[oracle@localhost ~]$ sqlplus / as sysdba
We are assuming that a1 and a2 are 2 schemas with a common table t1
[oracle@sap1]> expdp ‘”/ as sysdba”‘ directory=DATAPUMPDEMO_exp
dumpfile=a1-a2_tables logfile=a1-a2_tables tables=A1.T1,A2.T1
Export: Release 10.2.0.4.0 – 64bit Production on Thursday, 23 June, 2013 15:35:01
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
UDE-00012: table mode exports only allow objects from one schema
As you can see you can not export tables from different schemas by using “tables” parameter in expdp in Oracle 10g.
What you should do is create a table and insert the names of the tables to be exported to this table and use the “schemas” and “include” parameter in expdp command.
A1@ORCL > create table expdp_tables (table_name varchar2(30));
Table created.
A1@ORCL > insert into expdp_tables values (‘T1′);
1 row created.
A1@ORCL > commit;
Commit complete.
You only need to insert the distinct names of all the tables into “expdp_tables” schema. Put the names of the all the distinct schemas whose tables will be exported in the “schemas” parameter.
In that method you export all the tables in “expdp_tables” table from both schemas, this means if a specific table name exist in both schemas and you only want to export one of them, you can not do that.
[oracle@sap1]> expdp ‘”/ as sysdba”‘ directory=UURAL_DATAPUMPDEMO
dumpfile=a1-a2_tables logfile=a1-a2_tables schemas=A1,A2
INCLUDE=TABLE:\”IN \(SELECT table_name FROM a1.expdp_tables\)\”
Export: Release 10.2.0.4.0 – 64bit Production
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYS”.”SYS_EXPORT_SCHEMA_01″: “/******** AS SYSDBA” directory=UURAL_DATAPUMPDEMO dumpfile=a1-a2_tables logfile=a1-a2_tables schemas=A1,A2 INCLUDE=TABLE:”IN (SELECT table_name FROM a1.expdp_tables)”
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
. . exported “A1″.”T1″ 0 KB 0 rows
. . exported “A2″.”T1″ 0 KB 0 rows
Master table “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
******************************************************************************
Lets now try to export tables from different schemas in Oracle 11g database on a Linux server.
[oracle@localhost ~]$ expdp ‘”/ as sysdba”‘ directory=DATA_PUMP_DIR dumpfile=a1-a2_tables logfile=a1-a2_tables tables=A1.T1,A2.T1
Export: Release 11.2.0.2.0 – Production
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
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=a1-a2_tables logfile=a1-a2_tables tables=A1.T1,A2.T1
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “A1″.”T1″ 0 KB 0 rows
. . exported “A2″.”T1″ 0 KB 0 rows
Master table “SYS”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/home/oracle/app/oracle/admin/orcl/dpdump/a1-a2_tables.dmp
Job “SYS”.”SYS_EXPORT_TABLE_01″ successfully completed
As you can see, in Oracle 11g, you can export tables from different schemas by using “tables” parameter in Export Data Pump Utility and this is not possible in Oracle 10g.
