Easy way to import public synonyms?

Using export/import to update a development database, I normally do a schema (user) import. Is there an easy way to import public synonyms?

Using export/import to update a development database, I normally do a schema (user) import. Is there an easy way to import public synonyms?

Instead of using export/import for re-creating your PUBLIC synonyms, why not simply reverse-engineer the DDL statements to create the PUBLIC synonyms and then run that DDL on the development database? The following SQL can be run on your production database.

SPOOL create_pub_syn.sql
SELECT 'CREATE PUBLIC SYNONYM '||synonym_name||
 ' FOR '||table_owner||'.'||table_name||';'
FROM dba_synonyms
WHERE owner='PUBLIC';
SPOOL OFF

The result will be a text file "create_pub_syn.sql" which contains all the CREATE PUBLIC SYNONYM commands you need to execute in your development environment. An alternative is to use the DBMS_METADATA.GET_DDL package to extract the same DDL commands, but the above works quite nicely too.

Dig Deeper on Oracle database administration

Data Management
Business Analytics
SearchSAP
TheServerSide.com
Data Center
Content Management
HRSoftware
Close