RE: Unused Synonyms
- From: "Richard J. Goulet" <rgoulet@xxxxxxxxxx>
- To: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>, <Amrish.Kothari@xxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 19 Oct 2006 14:05:40 -0400
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@xxxxxxxxxx
: POWERING TRANSFORMATION
________________________________
From: Jacques Kilchoer [mailto:Jacques.Kilchoer@xxxxxxxxx]
Sent: Thursday, October 19, 2006 2:03 PM
To: Richard J. Goulet; Amrish.Kothari@xxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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;
/
Other related posts:
