Re: how to: list of synonyms (Oracle)?

From: PaulCinVT <paulcinvt_at_aol.com>
Date: 27 Jan 2000 19:21:32 GMT
Message-ID: <20000127142132.00196.00001791@nso-ch.aol.com>

In article <38904150.D9872813_at_Unforgetable.com>, Kenneth C Stahl <BlueSax_at_Unforgetable.com> writes:

>Marc wrote:
>>
>> Hi folks,
>>
>> I've got a corrupt synonym on my database. Problem is, I got a whole lot of
>> synonyms and I dont know which one is corrupt. So I want to delete or drop
>> all synonyms that contain 500 in the name.
>>
>> Question: How can I get a list of synynyms from e certain schema level?
>>
>> Marc
>
>You won't solve your problem by dropping and then re-creating the synonyms.
>It is perfectly valid to create a synonym for a non-existant object.
>
>What would be better is if you created a program which will sweep through
>your synonyms and test for the existance of each underlying object.
>

--Identifying Phantom Synonyms

/*
 Developers often complain about the error 'ORA-00980 Synonym translation no longer valid'.
 This error occurs because it is possible to create a synonym for  non-existing objects and objects of non-existing users.

 For example, you may be able to create a synonym for SCOTT.EMP  where SCOTT is not necessarily a schema in the database.

 When tables are dropped, synonyms owned by other users still point to  the objects. The presence of these phantom synonyms is not harmful,  but it's a clean habit to identify and drop them regulary.

 The following script drops the phantom synonyms from a development  environment.
*/

• Generate DDL to drop phantom synonyms in your database.
• Excludes SYS and SYSTEM users

spool dr_ph_syn.sql
set pages 0 feed off
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);

spool off

Paul in VT Received on Thu Jan 27 2000 - 13:21:32 CST