"dangling" synonyms: How do I get rid of them ?

From: Guy Dallaire <gdallair_at_criq.qc.ca>
Date: Mon, 18 Oct 1999 20:20:10 GMT
Message-ID: <380b8038.5304883@news.risq.qc.ca>

Hello,

I have a database here that has a LOT of synonyms pointing to nowhere. I mean, say

synonym usera.tablea points to userb.tableb but user userb and/or table tableb has been dropped.

I' trying to identify all the "dangling" synonyms in my oracle instance, I taught that the simple following SQL would help me but it misses some of them:

select 'drop synonym '||s.owner||'.'||synonym_name||';' from dba_synonyms s
where not exists(select 'x' from dba_objects t

                 where t.object_name=s.table_name and

t.owner=s.table_owner)
and s.db_link is null;

Please not that I added the db_link clause because I do not want to drop synonyms that refer to remote objects (which of course I would never find on the local machine...)

Thanks

---

Guy Dallaire, Sysadmin/DBA
Centre de recherche industrielle du Quebec Ste-Foy, QC, Canada
http://www.criq.qc.ca
e-mail: gdallair_at_criq.qc.ca
ICQ: 18846092 Received on Mon Oct 18 1999 - 15:20:10 CDT