How To Perform a Full Export And Exclude Certain Schemas Using The Data Pump API? (Doc ID 1340781.1)

Last updated on DECEMBER 03, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.

Goal

You want to perform a full export and exclude certain schema(s) using the Data Pump API (dbms_datapump).
The next example demonstrates how to perform this operation.

In this example,  you have the following environment, you have 20 schemas in your database:

SQL> select username from dba_users;

USERNAME
------------------------------
SYSTEM
SYS
MGMT_VIEW
SYSMAN
DBSNMP
TESTUSER
SCOTT
OUTLN
SI_INFORMTN_SCHEMA
ORDPLUGINS
XDB
WMSYS
EXFSYS
ANONYMOUS
ORDSYS
MDSYS
TSMSYS
ORACLE_OCM
DIP
MDDATA

20 rows selected.


You want to export the entire database but want to exclude the SYSTEM and SCOTT only while using the DataPump API.

To perform this operation from the command line for the data pump export (expdp) looks like:
expdp system/passwd directory=DATA_PUMP_DIR dumpfile=EXPDAT%U.DMP FULL=y exclude=schema:\"IN \(\'SCOTT\',\'SYSTEM\')\"




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