Q
Problem solve Get help with specific problems with your technologies, process and projects.

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 backup and recovery

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close