The Geek Diary

HowTos | Basics | Concepts

Oracle Database : script to create a “CREATE SYNONYM Script”

By

The following is a script that once run will generate another script that will include all the create synonym statements for all those in the database, both private and public.

Pre-requisites

1. This script must be run by a user with the DBA role.

The script

Running this script will in turn create a script to build all the synonyms in the database. This created script, create_synonyms.sql, can be run by any user with the DBA role or with the ‘CREATE ANY SYNONYM’ and ‘CREATE PUBLIC SYNONYM’ system privileges.

NOTE: This script does not capture synonyms created on tables owned by the ‘SYS’ user.
set verify off
set feedback off
set termout off
set echo off
set pagesize 0

set termout on
select 'Creating synonym build script...' from dual;
set termout off

spool create_synonyms.sql

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
/
spool off

exit

Sample output:

CREATE PUBLIC SYNONYM temp$lob FOR system.def$_temp$lob;                        
CREATE PUBLIC SYNONYM product_profile FOR system.product_privs;                 
CREATE PUBLIC SYNONYM product_user_profile FOR system.product_privs;            
CREATE PUBLIC SYNONYM user_profile FOR system.user_privs;                       
CREATE PUBLIC SYNONYM table_dependency FOR system.table_dependency;             
CREATE PUBLIC SYNONYM slt FOR scott.linktest@V815.WORLD;                        
CREATE SYNONYM SYS.def$_aqcall FOR system.def$_aqcall;                          
CREATE SYNONYM SYS.def$_calldest FOR system.def$_calldest;                      
CREATE SYNONYM SYS.def$_schedule FOR system.def$_schedule;                      
CREATE SYNONYM SYS.def$_error FOR system.def$_error;                            
CREATE SYNONYM SYS.def$_defaultdest FOR system.def$_defaultdest;                
CREATE SYNONYM SYS.def$_lob FOR system.def$_lob;

You May Also Like