RE: Unused Synonyms

From: Richard J. Goulet <rgoulet_at_kanbay.com>
Date: Thu, 19 Oct 2006 14:05:40 -0400
Message-ID: <C3EE2ADD31ACF64DAB1B236044A1968D46A73B@miaexc01.kanbay.com>

Jacques,  

    Well made recommendation. In the past I've only had to deal with synonyms where the base table disappeared so it rendered an ORA-0942 message.     

Dick Goulet, Senior Oracle DBA

45 Bartlett St Marlborough, Ma 01752, USA Tel.: 508.573.1978 |Fax: 508.229.2019 | Cell:508.742.5795

RGoulet_at_kanbay.com
: POWERING TRANSFORMATION  

---

From: Jacques Kilchoer [mailto:Jacques.Kilchoer_at_quest.com] Sent: Thursday, October 19, 2006 2:03 PM To: Richard J. Goulet; Amrish.Kothari_at_in.bosch.com; oracle-l_at_freelists.org Subject: RE: Unused Synonyms

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:05:40 CDT