Oracle Data Pump Schema Export and Public Synonyms

Posted in: Technical Track

While testing a migration today with one of our clients, I figured out that schema export using Data Pump doesn’t capture public synonyms on the objects in this schema.  Update: This is actually the same behavior as old Export utility.

I can think of two workarounds:

  • Use Data Pump to export/import public schema including only synonyms but I couldn’t think of an easy way to filter only public synonyms on objects in specified schemas.
  • Generate a script that creates public synonyms. You can run the following on the source database, for example:
SELECT 'CREATE PUBLIC SYNONYM ' || synonym_name || ' FOR '
|| table_owner || '.' || table_name || ';' cmd
FROM dba_synonyms
WHERE TABLE_OWNER IN ([list of schemas]) AND owner='PUBLIC';

Does anyone know how to make Data Pump include public synonyms with schema export?

Updated:
Thanks to Paul for Export behavior correction. Also see below Andrew’s more complete version of the query including database links.

Concerned which objects are exported by Data Pump on full, schema, and table levels? Check views – DATABASE_EXPORT_OBJECTS, SCHEMA_EXPORT_OBJECTS, and TABLE_EXPORT_OBJECTS.

I still couldn’t get Data Pump filter out only synonyms I need. So far the best I’ve come up with:
INCLUDE=SYNONYM:"IN (SELECT synonym_name FROM dba_synonyms WHERE table_owner='TEST')"
However, this also captures private synonyms that happen to have the same name.

Interested in working with Alex? Schedule a tech call.

About the Author

What does it take to be chief technology officer at a company of technology experts? Experience. Imagination. Passion. Alex Gorbachev has all three. He’s played a key role in taking the company global, having set up Pythian’s Asia Pacific operations. Today, the CTO office is an incubator of new services and technologies – a mini-startup inside Pythian. Most recently, Alex built a Big Data Engineering services team and established a Data Science practice. Highly sought after for his deep expertise and interest in emerging trends, Alex routinely speaks at industry events as a member of the OakTable.

9 Comments. Leave new

Good point!
However, just to clarify, EXP only captures public synonyms in full export mode (full=y), not schema-level, or table level mode.

Reply

Don’t forget about DB links…

select ‘CREATE ‘|| decode(owner,’PUBLIC’,’PUBLIC ‘,null) ||
‘SYNONYM ‘ || decode(owner,’PUBLIC’,null, owner || ‘.’) ||
lower(synonym_name) || ‘ FOR ‘ || lower(table_owner) ||
‘.’ || lower(table_name) ||
decode(db_link,null,null,’@’||db_link) || ‘;’
from sys.dba_synonyms
where table_owner != ‘SYS’
order by owner
/

Reply
Alex Gorbachev
November 1, 2006 6:59 pm

Thanks andrew,
Good catch about DB links. Isn’t this a beauty of sharing your experience?
The more you share about what you do and come up with, the stronger your procedures and knowedge are. Amazing paradox. Huh?

Paul,
I guess there is no difference to Data Pump then! :-) Both, old Export and Data Pump, do not process public synonyms on schema export.
Hurray! I just tested it again becuase I did a small test earlier prior this post to make sure I didn’t miss something and you know what? I did miss one thing. I gazed quickly on “. exporting PUBLIC type synonyms” line and mistakenly took it for confirmation that public synonyms are handled by old Export. On the second look only I noticed that it’s talking about public types.

Reply
Alex Gorbachev
November 1, 2006 8:36 pm

Updated the original post with that.

Reply

yes, an old thread, but I’m working on a lot of datapump moves these days.

Related to PUBLIC SYNONYMS but only for the schema you are moving

Try this:

INCLUDE=SYNONYM:”IN(SELECT synonym_name FROM dba_synonyms WHERE owner = ‘PUBLIC’ and table_owner=’TEST’)”

That worked for me.

Reply
Kirill Loifman
October 14, 2011 7:42 am

Do not expect exp or expdp extract public objects during schema export by default.
You have to transfer them all BEFORE the import to avoid errors. These are, other referenced users, roles, public synonyms, db links, user_profiles, etc.

— Kirill Loifman, dadbm.com

Reply

I was wrestling with Bradd’s INCLUDE line for impdp and it wasn’t working wasn’t working and then I realized that OF COURSE it wasn’t going to work because the select is empty because there aren’t any public synonyms yet.

I’d like to just import the public synonyms for the schema(s) that I’m importing. Darn – maybe next version.

(yes, I know that I can use a script to generate the create public synonym statements, but I’d like to just get it out of the datapump.dmp file)

Reply
Kirill Loifman
January 20, 2012 9:57 am

Unfortunately Bradd’ solution did not work for me also. The export session is simply hanging with it. However after some tests I found a fix.
See here: http://www.dadbm.com/2012/01/how-to-export-public-database-objects-with-oracle-data-pump/

— Kirill Loifman, dadbm.com

Reply

the suggested query of sys.dba_synonyms drops DDL for synonyms over dblinks…check ‘where db_link is not null.’ Thanks for the query. It helps.

Version 11.2 and no non-messy way to move users around. How long, how long….

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *