RE: Unused Synonyms

From: Richard J. Goulet <rgoulet_at_kanbay.com>
Date: Thu, 19 Oct 2006 10:17:24 -0400
Message-ID: <C3EE2ADD31ACF64DAB1B236044A1968D46A727@miaexc01.kanbay.com>

Amkot,  

    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;
/  

USE AT YOUR OWN PERIL.      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: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Amrish Kothari (RBIN/EDM1)
Sent: Thursday, October 19, 2006 5:13 AM To: oracle-l_at_freelists.org
Subject: Unused Synonyms

Hello All,  

How can i find in the database al the synonyms which are not used or unused , but exists in the database. ?      

Thanks in advance,
Amkotz

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

Received on Thu Oct 19 2006 - 09:17:24 CDT