RE: Unused Synonyms

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Thu, 19 Oct 2006 11:02:49 -0700
Message-ID: <22EC50E60931494FA666A8CF8260C45B5D9AE3@ALVMBXW05.prod.quest.corp>

Mr. Goulet, may I suggestour useful PL/SQL would benefit from checking for error codes 942 AND ORA-00980 (synonym translation is no longer valid):  

SQL> create public synonym s for non_existing_table ; Synonyme créé.
SQL> select count (*) from s ;
select count (*) from s

                      *

ERREUR à la ligne 1 :
ORA-00980: La traduction de synomymes n'est plus valide

---

De la part de Richard J. Goulet

    User defined synonyms should never be a problem, but public ones definitely can clutter up things & potentially cause some security concerns in the future. The following PL/SQL block should take care of those easily. It's based on can I count the number of rows in the source table. If not then bye-bye. I would run this as a DBA who has select any table priviledges since access to the subject source table could be restricted.  

declare
  stmt varchar2(1000);
  dummy number;
begin
  for a in (select synonym_name from dba_synonyms

            where owner = 'PUBLIC') loop
     stmt := 'select count(*) from '||a.synonym_name;
     begin
       execute immediate stmt into dummy;
     exception when others dummy := sqlcode;
                           if(dummy = -942) then
                            stmt := 'drop public synonym '||a.synonym_name;
                            execute immediate stmt;
                           else
                             dbme_outptu.put_line(stmt||chr(10)||sqlerrm);
                           end if;
     end;

  end loop;
end;
/

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Oct 19 2006 - 13:02:49 CDT