--set echo off
--
--  co_pubsynonyms_missing.sql

--  Synonyms - Find objects for a specific owner that don't have a public synonym
--
--  Usage: @co_pubsynonyms_missing owner
--  Example: @co_pubsynonyms_missing sysadm
--
--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=co_pubsynonyms_missing.ddl
spool &&out_file
--
SELECT 'synonym '||owner|| '.' ||rtrim(OBJECT_NAME)||';'
FROM dba_objects a
WHERE object_type IN ('TABLE','PACKAGE','PROCEDURE','FUNCTION','VIEW','SEQUENCE')
and owner = '&&1'
AND not exists (SELECT null
                FROM   dba_synonyms
                WHERE  owner = 'PUBLIC'
                  AND  dba_synonyms.synonym_name = a.object_name
                  AND  dba_synonyms.table_owner  = upper('&&1'));
spool off
undefine owner
set doc off
set feedback on
set pagesize 30
--set termout on
--set echo on
--start &&out_file
-- exit