Drop orphaned synonyms

  SELECT 'drop '
       || DECODE (s.owner,
                  'PUBLIC', 'PUBLIC SYNONYM ',
                  'SYNONYM ' || s.owner || '.')
       || s.synonym_name
       || ';'
  FROM dba_synonyms s
 WHERE table_owner NOT IN ('SYSTEM', 'SYS') AND db_link IS NULL
       AND NOT EXISTS
                  (SELECT 1
                     FROM dba_objects o
                    WHERE s.table_owner = o.owner
                          AND s.table_name = o.object_name)

  

Recompiling Public Synonyms

SET LONG 20000
set LONGCHUNKSIZE 20000
set PAGESIZE 0
set LINESIZE 1000
set FEEDBACK OFF
set VERIFY OFF
select 'alter public synonym '||object_name||' compile;' from all_objects where status='INVALID' and object_type='SYNONYM';

A public synonym gets invalidated when the underlying object changes

	SQL> select owner, object_type, status from dba_objects where object_name = 'CI_VALIDATE_SYSTEM_MAPPING';

	            Object
	Owner       Type       Status
	-------- ------------ ----------
	PUBLIC   SYNONYM      INVALID
	CI       PROCEDURE    VALID


    SQL> alter public synonym CI_VALIDATE_SYSTEM_MAPPING compile;

    Synonym altered.

 
    SQL> select owner, object_type, status from dba_objects where object_name = 'CI_VALIDATE_SYSTEM_MAPPING';

               Object
    Owner       Type       Status
    -------- ------------ ----------
    PUBLIC   SYNONYM      VALID
    CI       PROCEDURE    VALID

Duplicate Public Synonymns

  select t1.owner owner, t1.synonym_name synonym_name
from dba_synonyms t1
where t1.synonym_name in
(select SYNONYM_NAME
from dba_synonyms
where owner in ('ILM_GPP_APP','PUBLIC')
  and table_owner in ('ILM_GPP')
group by SYNONYM_NAME
having count(*) > 1)
-- and t1.owner = 'PUBLIC'
order by 2,1;

set term off
spool drop_dup_pub_syn
select 'drop public synonym '||t1.synonym_name||';'||chr(10)
from dba_synonyms t1
where t1.synonym_name in
(select SYNONYM_NAME
from dba_synonyms
where owner in ('ILM_GPP_APP','PUBLIC')
  and table_owner in ('ILM_GPP')
group by SYNONYM_NAME
having count(*) > 1)
and t1.owner = 'PUBLIC'
order by t1.synonym_name;
spool off


spool restore_drop_dup_pub_syn.lst
select 'create public synonym '||t1.synonym_name||' for ilm_gpp.'||table_name||';'||chr(10)
from dba_synonyms t1
where t1.synonym_name in
(select SYNONYM_NAME
from dba_synonyms
where owner in ('ILM_GPP_APP','PUBLIC')
  and table_owner in ('ILM_GPP')
group by SYNONYM_NAME
having count(*) > 1)
and t1.owner = 'PUBLIC'
order by t1.synonym_name;
spool off
set term on
  

Loop Chain of Synonyms

This happens when you have a "dangling" synonym, because it points to itself.

For example:

SQL> create table blah ( a number );

Table created.

SQL> create public synonym blah for blah;

Synonym created.

SQL> drop table blah;

Table dropped.

SQL> select * from blah;
select * from blah
              *
ERROR at line 1:
ORA-01775: looping chain of synonyms