On this page
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:
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.
Learn more about Pythian by reading the following blogs and articles.
With Pythian, you can accomplish your data transformation goals and more.
© 2025 All rights reserved.