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

From: Tapan Trivedi <tapan.trivedi_at_abbnm.com>
Date: Tue, 19 Oct 1999 03:48:49 -0500
Message-ID: <380C3071.370BF865@abbnm.com>

Guy,
Hi Here is a script. May help.

 select 'drop '||decode(owner,'PUBLIC',' public ',null)||' synonym             

'||decode(owner,'PUBLIC',null,owner||'.')||synonym_name||';'

      from dba_synonyms s where owner not in('SYSTEM','SYS') and db_link is

      null 
                                                              and not
exists 
           (select  1  from all_objects o 
                       where object_type in('TABLE','VIEW','SYNONYM', 
                                           'SEQUENCE','PROCEDURE', 
                                           'PACKAGE','FUNCTION') 
                                            and s.table_owner=o.owner 
                                            and

s.table_name=o.object_name)

                                                        Tapan

Guy Dallaire wrote:
>
> 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 Tue Oct 19 1999 - 03:48:49 CDT