dropping public synonyms?
Kirill Richine
Hi!
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
do it manually for each synonym? If yes, then which table are they in
and what would be the cleanest way to remove them?
Thank you.
k&
Sheilah Scheurich
On 5 May 1997 17:18:29 GMT, kir...@cs.ualberta.ca (Kirill Richine)
wrote:
try this:
set feedback off
set pagesize 0
spool out.sql
select 'drop public synonym '||synonym_name||';' from dba_synonyms
where owner = &owner;
spool off
@out.sql
Should work... It may create a few errors at the end, but this will
work.
sheilah scheurich
dba
sche...@sprynet.com
Jonathan Sell
Kirill Richine wrote:
>
> Hi!
>
> 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
> do it manually for each synonym? If yes, then which table are they in
> and what would be the cleanest way to remove them?
>
> Thank you.
> k&
Yes, you can drop them (if you're the DBA) but no, you can't tell from
the
database who created them. Regular users do not normally have the priv.
to create public synonyms. Once created, the owner is 'PUBLIC' -- only
way to drop them is "DROP PUBLIC SYNONYM ..."
JCS
--
___ ___
| | | |
|__#####__| Jonathan C. Sell
js...@bio.ri.ccf.org
__#####__ The Department of Biostatistics Phone:
216-444-0585
| ##### | The Cleveland Clinic Foundation FAX:
216-444-8023
|___| |___|
"Do or do not. There is no 'try'."
-Yoda
Thomas Kyte
On 6 May 1997 15:00:12 GMT, kir...@cs.ualberta.ca (Kirill Richine) wrote:
>Sheilah Scheurich (sche...@sprynet.com) 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.
>k&
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
tk...@us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation