Views expressed here are solely that of my own. Please make sure that you test the code/queries that appear on my blog before applying them to the production environment.

Thursday, June 23, 2011

How to export tables from multiple schemas with Oracle Data Pump in Oracle 10g and 11g databases

First try it in an Oracle 10g database on an AIX server.

First create the sample export folder which export files will be written.
This folder should be created by unix user which is the owner of Oracle database (here "oracle" Unix OS user) or if created by another OS user, the directory permissions should be arranged so that Oracle owner OS user "oracle" can write to this folder.
[oracle@srvdb01]:/oracle > mkdir -p /transfer/uural/datapumpdemo
[oracle@srvdb01]:/oracle > cd /transfer/uural
[oracle@srvdb01]:/transfer/uural > ls -ld datapumpdemo
drwxr-xr-x    2 oracle   dba            4096 Jun 22 15:06 datapumpdemo
[oracle@srvdb01]:/transfer/uural > cd datapumpdemo
[oracle@srvdb01]:/transfer/uural/datapumpdemo > ls -l
total 0
[oracle@srvdb01]:/transfer/uural/datapumpdemo >

Now create a new directory in the database pointing to the above export folder.
[oracle@srvdb01]:/transfer/uural/datapumpdemo > sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jun 22 15:28:09 2011

Copyright (c) 1982, 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

SYS@ORCL AS SYSDBA> create or replace directory uural_datapumpdemo as '/transfer/uural/datapumpdemo';

Directory created.

SYS@ORCL AS SYSDBA> desc dba_directories
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 DIRECTORY_NAME                            NOT NULL VARCHAR2(30)
 DIRECTORY_PATH                                     VARCHAR2(4000)

SYS@ORCL AS SYSDBA> column directory_path format a50
SYS@ORCL AS SYSDBA> set lines 200
SYS@ORCL AS SYSDBA> r
  1* select * from dba_directories where directory_name = 'UURAL_DATAPUMPDEMO'

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------------------------------
SYS                            UURAL_DATAPUMPDEMO             /transfer/uural/datapumpdemo


Lets create a sample schemas with some of empty tables in them.
SYS@ORCL AS SYSDBA> create user u0001 identified by "u0001_pass";

User created.

SYS@ORCL AS SYSDBA> grant create session to u0001;

Grant succeeded.

SYS@ORCL AS SYSDBA> grant create table to u0001;

Grant succeeded.

SYS@ORCL AS SYSDBA> alter user u0001 quota 5G on users;

User altered.

SYS@ORCL AS SYSDBA> GRANT READ, WRITE ON DIRECTORY SYS.UURAL_DATAPUMPDEMO TO U0001;

Grant succeeded.

SYS@ORCL AS SYSDBA> create user u0002 identified by "u0002_pass";

User created.

SYS@ORCL AS SYSDBA> grant create session to u0002;

Grant succeeded.

SYS@ORCL AS SYSDBA> grant create table to u0002;

Grant succeeded.

SYS@ORCL AS SYSDBA> alter user u0002 quota 5G on users;

User altered.

SYS@ORCL AS SYSDBA> conn u0001/u0001_pass
Connected.
U0001@ORCL > create table t0001 (col1 number);

Table created.

U0001@ORCL > conn u0002/u0002_pass
Connected.
U0002@ORCL > create table t0001 (col1 number);

Table created.

Lets try to export some tables from only a single schema in the database.
[oracle@srvdb01]:/transfer/uural/datapumpdemo > expdp u0001/u0001_pass directory=UURAL_DATAPUMPDEMO 
dumpfile=u0001_tables logfile=u0001_tables tables=T0001

Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 23 June, 2011 15:29:05

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 "U0001"."SYS_EXPORT_TABLE_01":  u0001/******** directory=UURAL_DATAPUMPDEMO dumpfile=u0001_tables logfile=u0001_tables tables=T0001
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 "U0001"."T0001"                                 0 KB       0 rows
Master table "U0001"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for U0001.SYS_EXPORT_TABLE_01 is:
  /transfer/uural/datapumpdemo/u0001_tables.dmp
Job "U0001"."SYS_EXPORT_TABLE_01" successfully completed at 15:32:39

[oracle@srvdb01]:/transfer/uural/datapumpdemo > ls -l
total 136
-rw-r-----    1 oracle   dba           61440 Jun 23 15:32 u0001_tables.dmp
-rw-r--r--    1 oracle   dba            1043 Jun 23 15:32 u0001_tables.log
[oracle@srvdb01]:/transfer/uural/datapumpdemo >

Now try to export tables from two different schemas, since we are going to export tables from two schemas we will use "SYS" as the userid parameter in expdp command.
[oracle@srvdb01]:/transfer/uural/datapumpdemo > expdp '"/ as sysdba"' directory=UURAL_DATAPUMPDEMO 
dumpfile=u0001-u0002_tables logfile=u0001-u0002_tables tables=U0001.T0001,U0002.T0001                                                     

Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 23 June, 2011 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

[oracle@srvdb01]:/transfer/uural/datapumpdemo >

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.

U0001@ORCL > create table expdp_tables (table_name varchar2(30));

Table created.

U0001@ORCL > insert into expdp_tables values ('T0001');

1 row created.

U0001@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@srvdb01]:/transfer/uural/datapumpdemo > expdp '"/ as sysdba"' directory=UURAL_DATAPUMPDEMO 
dumpfile=u0001-u0002_tables logfile=u0001-u0002_tables schemas=U0001,U0002 
INCLUDE=TABLE:\"IN \(SELECT table_name FROM u0001.expdp_tables\)\"                                                             

Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 23 June, 2011 15:50:35

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=u0001-u0002_tables logfile=u0001-u0002_tables schemas=U0001,U0002 INCLUDE=TABLE:"IN (SELECT table_name FROM u0001.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 "U0001"."T0001"                                 0 KB       0 rows
. . exported "U0002"."T0001"                                 0 KB       0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /transfer/uural/datapumpdemo/u0001-u0002_tables.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:58:01

[oracle@srvdb01]:/transfer/uural/datapumpdemo >


Lets now try to export tables from different schemas in Oracle 11g database on a Linux server.
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Jun 23 07:07:24 2011

Copyright (c) 1982, 2010, Oracle.  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

SQL> column directory_path format a50
SQL> select * from dba_directories where directory_name = 'DATA_PUMP_DIR'

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------------------------------
SYS                            DATA_PUMP_DIR                  /home/oracle/app/oracle/admin/orcl/dpdump/

SQL> 

Check the number of files in the destination folder before the export
[oracle@localhost ~]$ ls -l /home/oracle/app/oracle/admin/orcl/dpdump/
total 4
-rw-r----- 1 oracle oracle 116 Oct  2  2010 dp.log
[oracle@localhost ~]$ 

Prepare the sample schema and objects
SQL> create user u0001 identified by "u0001_pass";

User created.

SQL> grant create session to u0001;

Grant succeeded.

SQL> grant create table to u0001;

Grant succeeded.

SQL> create user u0002 identified by "u0002_pass";

User created.

SQL> grant create session to u0002;

Grant succeeded.

SQL> grant create table to u0002;

Grant succeeded.

SQL> conn u0001/u0001_pass
Connected.
SQL> create table t0001 (col1 number);

Table created.

SQL> conn u0002/u0002_pass
Connected.
SQL> create table t0001 (col1 number);

Table created.


Try the export in 11g
[oracle@localhost ~]$ expdp '"/ as sysdba"' directory=DATA_PUMP_DIR dumpfile=u0001-u0002_tables 
logfile=u0001-u0002_tables tables=U0001.T0001,U0002.T0001

Export: Release 11.2.0.2.0 - Production on Thu Jun 23 07:17:53 2011

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=u0001-u0002_tables logfile=u0001-u0002_tables tables=U0001.T0001,U0002.T0001 
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 "U0001"."T0001"                                 0 KB       0 rows
. . exported "U0002"."T0001"                                 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/u0001-u0002_tables.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 07:18:48

[oracle@localhost ~]$  ls -l /home/oracle/app/oracle/admin/orcl/dpdump/
total 96
-rw-r----- 1 oracle oracle   116 Oct  2  2010 dp.log
-rw-rw---- 1 oracle oracle 86016 Jun 23 07:18 u0001-u0002_tables.dmp
-rw-rw-r-- 1 oracle oracle  1167 Jun 23 07:18 u0001-u0002_tables.log
[oracle@localhost ~]$ 

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.

1 comment:

azerty said...

Does the same apply for impdp?