How to export public database objects with Oracle Data Pump

January 20, 2012 by Leave a comment 

Neither original Oracle Export (exp) utility nor Data Pump (expdp) can capture related public Oracle database objects during schema(s) export. During the schema(s) copy or refresh you have to transfer them to the target database manually BEFORE the import to avoid related import errors. These public objects are:

–  referenced users and roles

–  public synonyms

– public database links

–  user profiles

–  etc.

You can use a few workarounds though to capture those public database objects manually on the source database using for example a dynamic SQL or Oracle dbms_metadata package (see an example in my article Extract DDL of Oracle objects with dbms_metadata package).

There are a few suggestions on the Internet how to extract those public objects using Data Pump (expdp) utility which did not work unfortunately for me. Some of them fail; others simply hang my export job. After some testing however I found a solution.

First, we have to use FULL=Y export option since in case of a schema level export this will not work.
Second, we use INCLUDE clause with required object type specification and a related SQL statement predicate.

See below an example of capturing public synonyms for a particular schema using Oracle Data Pump (expdp) utility. Just place below Data Pump options in a parameter file:

INCLUDE=PUBLIC_SYNONYM/SYNONYM:"IN(SELECT synonym_name FROM dba_synonyms WHERE table_owner='TEST')"

In case you want to export some other objects check out the following view DATABASE_EXPORT_OBJECTS

Please comment with real working scenarios for other public objects in my list. Roles with grants would be interesting one.

Enjoyed this article? Please share it with others using the social site of your choice:

Add a Comment

We welcome thoughtful and constructive comments from readers.
If you want your own picture to show with your comment?
Go get a Globally Recognized Avatar!

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email