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 MAY 11, 2021

Applies to:

Oracle Database - Personal Edition - Version 10.1.0.2 to 11.1.0.7 [Release 10.1 to 11.1]
Oracle Database - Standard Edition - Version 10.1.0.2 to 11.1.0.7 [Release 10.1 to 11.1]
Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.1.0.7 [Release 10.1 to 11.1]
Information in this document applies to any platform.

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product.
Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

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


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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Goal
Solution
 Solution 1: Use combination of SCHEMAS and INCLUDE parameters.
 Solution 2: Use multiple Data Pump jobs.
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.