My Oracle Support Banner

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

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Enterprise Edition - Version and later
Oracle Database - Personal Edition - Version and later
Oracle Database - Standard Edition - Version and later
Enterprise Manager for Oracle Database - Version and later
Information in this document applies to any platform.


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 \

Export: Release - 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 - 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 \

Export: Release - 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 - 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:

This is implemented in version 11.2.

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



To view full details, 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 a vibrant support community of peers and Oracle experts.