Re: dropping public synonyms?

From: Thomas Kyte <>
Date: 1997/05/06
Message-ID: <3374632a.7334406_at_newshost>#1/1

On 6 May 1997 15:00:12 GMT, (Kirill Richine) wrote:

>Sheilah Scheurich ( wrote:
>: >I was wondering if it is possible to drop public synonyms created by
>: >an oracle user. The drop user cascade does not do it. Would I have to

>: select 'drop public synonym '||synonym_name||';' from dba_synonyms
>: where owner = &owner;
>The problem is that the owner of all public synonyms is 'PUBLIC'
>however I don't want to remove all public synonyms in the database,
>only the ones created by this user.
>So far as I understand, once the public synonym is created, it is
>intractable to who created it. Is it right?
>Thank you.

Yes, they are untraceable, you can't tell who created a public synonym.

What you probably want to do though is drop all public synonyms that point to objects that no long exist as you have dropped the user. To do this you can

select 'drop public synonym ' || synonym_name || ';' from dba_synonyms
where TABLE_OWNER = upper('&1')

Thomas Kyte
Oracle Government
Bethesda MD -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue May 06 1997 - 00:00:00 CEST

Original text of this message