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;