SCRIPT FOR CREATING SYNONYMS
=======================================

REM
REM SCRIPT FOR CREATING SYNONYMS
REM
REM This script must be run by a user with the DBA role.
REM
REM This script is intended to run with Oracle7.
REM
REM Running this script will in turn create a script to build all the
REM synonyms in the database. This created script, create_synonyms.sql,
REM can be run by any user with the DBA role or with the 'CREATE ANY
REM SYNONYM' and 'CREATE PUBLIC SYNONYM' system privileges.
REM
REM NOTE: This script does not capture synonyms created on tables owned
REM by the 'SYS' user.
REM
REM Only preliminary testing of this script was performed. Be sure to test
REM it completely before relying on it.
REM

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

REM ---------------------------------------------------------------------------