Search the Site

Sponsors

bottom corner

Export Oracle synonyms to text file

This article provides an example for Oracle DBAs to export synonyms to a flat file, which may be useful as an alternative to their regular system backups as well as a means to quickly create synonyms in a testing database that mirror another database.

This page is filed under keyword(s): oracle.

This is achieved by using the sys.dba_synonyms table, which means these queries must be done using an account with proper privileges. Note the output file has the extension .sql, which suggests that it can be run directly from SQL*Plus as part of an automated script, if necessary.

set linesize 256;
spool c:\oracleSynonyms.sql;

select 'create or replace '|| decode(owner,'PUBLIC','public ',null) ||  
'synonym ' || decode(owner,'PUBLIC',null, lower(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 not in('SI_INFORMTN_SCHEMA','SYS','SYSTEM','ORDSYS','XDB','CTXSYS','DMSYS','EXFSYS','MDSYS','SYSMAN','WKSYS','WMSYS')
order by owner, table_name;

spool off;

Did you find this page useful? Please consider browsing other articles or subscribing to the RSS feed to keep up with latest.

This page is filed under keyword(s): oracle.
Author: C. Peter Chen
Last updated: 26 Aug 2008

bottom corner