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;