Synonyms - Creates public synonyms for all objects in all schemas (except sys, system) that don't already have a public synonym. 
set echo off
----------------------------------------------------------------------------------------
--
--  Name: c_pubsynonyms_nonsys.sql
--
--  Desc: Synonyms - Creates public synonyms for all objects in all schemas (except sys, system) that don't already have a public synonym.
--
--  Author: Michael Pearce
--
--  Usage: @c_pubsynonyms_nonsys
--
--  Revision History:
--
--  DATE       WHO		   WHAT
--  ---------  ----------  ------------------
--
--
----------------------------------------------------------------------------------------
set termout off
set verify off
set doc off
set feedback off
set recsep off
set pagesize 0
--
define lsize=200
set linesize &&lsize
--
define out_file=c_pubsynonyms.ddl
spool &&out_file
--
SELECT 'create public synonym '||rtrim(OBJECT_NAME)||' for '||OWNER|| '.' ||rtrim(OBJECT_NAME)||';'
FROM all_objects
WHERE object_type IN ('TABLE','PACKAGE','PROCEDURE','FUNCTION','VIEW','SEQUENCE')
AND OWNER not in ('SYSTEM', 'SYS')
AND not exists (SELECT null
                FROM   dba_synonyms
                WHERE  owner = 'PUBLIC'
                  AND  dba_synonyms.synonym_name = all_objects.object_name
                  AND  dba_synonyms.table_owner  = all_objects.owner);
spool off
set doc off
set feedback on
set pagesize 30
set termout on
set echo on
start &&out_file
-- exit