RE: Unused Synonyms
- From: "Richard J. Goulet" <rgoulet@xxxxxxxxxx>
- To: <Amrish.Kothari@xxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 19 Oct 2006 10:17:24 -0400
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@xxxxxxxxxx
: POWERING TRANSFORMATION
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Amrish Kothari
(RBIN/EDM1)
Sent: Thursday, October 19, 2006 5:13 AM
To: oracle-l@xxxxxxxxxxxxx
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
Other related posts:
