Export/Import DataPump Parameter TABLES - How to Export and Import Tables Residing in Different Schemas (Doc ID 277905.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database - Personal Edition - Version 10.1.0.2 and later
Oracle Database - Standard Edition - Version 10.1.0.2 and later
Enterprise Manager for Oracle Database - Version 10.1.0.2 and later
Information in this document applies to any platform.
***Checked for relevance on 11-Mar-2016***

Goal

With the classic export client you were able to export with one single export session, tables that were owned by different users.

For example to export the following tables:
- table EMP owned by user SCOTT
- table EMPLOYEES owned by user HR
- table CATEGORIES_TAB owned by user OE

% exp system/manager FILE=exp_tabs.dmp LOG=exp_tabs.log \
TABLES=scott.emp,hr.employees,oe.categories_tab

Export: Release 11.1.0.6.0 - Production on Wed Nov 28 10:04:10 2007
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8ISO8859P15 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                      EMPLOYEES        107 rows exported
Current user changed to OE
. . exporting table                 CATEGORIES_TAB         22 rows exported
. . exporting table     PRODUCT_REF_LIST_NESTEDTAB        288 rows exported
. . exporting table SUBCATEGORY_REF_LIST_NESTEDTAB         21 rows exported
Export terminated successfully without warnings.


However, with Export Data Pump you get the following error:

% expdp system/manager DIRECTORY=my_dir \
DUMPFILE=expdp_tabs.dmp LOGFILE=expdp_tabs.log \
TABLES=scott.emp,hr.employees,oe.categories_tab

Export: Release 11.1.0.6.0 - Production on Wednesday, 28 November, 2007 10:07:13
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
UDE-00012: table mode exports only allow objects from one schema


This is caused by a restriction for the TABLES parameter of Data Pump:
"The table name that you specify can be preceded by a qualifying schema name. All table names specified must reside in the same schema."

An enhancement request has been filed to make the syntax similar to that of the original export client:
<Bug 6860716> - EXPORT&FILTER TABLES FROM MULTIPLE SCHEMAS IN DATA PUMP

This is implemented in version 11.2.

How to achieve the same kind of export with Oracle Data Pump ?

 

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms