delete all objects for a particular schema

Hello all..

I have a user who has created buch of tables/views/indexex/...etc. IS there acommand that will delete all his objects.

I know by using drop user cascade; will drop the user with tables....but here i want a similar command without dropping the user.

Thanks
Ron

Write very small script like:

spool del_objects.txt
select 'drop '||object_type||' '||owner||'.'||object_name||';'
from all_objects
where owner = 'UR_USER';
spool off;

or use dynamic sql for it.

Shestakov

Is it all_objects or user_objects..? I think all_objects is not appropriate here.


Thomas

Quote:

---

Originally posted by Thomasps

Is it all_objects or user_objects..? I think all_objects is not appropriate here.

---

Given script can be executed by any user having necessary priviledges. You can also make use of USER_OBJECTS view, but in that case, you must connect to the user whos objects needs to be dropped and then execute the statement.

Sameer

And of course the above script will have to be run N times (where N is some hared-to-define integer > 1) in order to wipe out all objects from a schema. Provided of course that there are some referential integrity constraints among the tables....

I just "drop user x cascade;" and recreate it.

OOOPS - just re-read the original post - sorry!

And on top of that, there are some objects you can't drop if you are not connected as their owner, not even if you are connected as SYS....

So the only proper way is to drop user with cascade - no other bullet proof method, unless you can connect as the owner of the schema you want to wipe out.

Hi Jmodic,

What object would that be than.

I would run the drop query on dba_objects but before that I would make two meta query to disable constraints. 1st foreign keys than rest of the constraints.

Tycho

Quote:

---

Originally posted by tycho
What object would that be than.

---

Private database links. Only the owner himself can drop those.

JModic,

Ever tried deleting from sys.link$

Tycho

Quote:

---

Originally posted by tycho
JModic,

Ever tried deleting from sys.link$

Tycho

---

As a matter of fact, I did (among other database "hacking" games), however I can't see how this is relevant here. And BTW, I would not be too comfortable with publicly suggesting to mess up with database dictionary on an open forum like this one - some inexperienced user might even take it seriously and try something simmilar on the production system.....

The isue is not if something is possible at all (if it would not be, how would oracle then do it during "DROP USER" action), the isue is if you can do it with supported actions, like legal DDL statements and PL/SQL APIs. With this in mind I say again: It is not possible to create bullet-proof script to drop all the objects from user schema if you can not connect as the schema owner during the script execution.

Quote:

---

Originally posted by jmodic
And of course the above script will have to be run N times (where N is some hared-to-define integer > 1) in order to wipe out all objects from a schema. Provided of course that there are some referential integrity constraints among the tables....

---

Yes this is absolutly correct.

But somebody may try to write "real" script that has to bulid tree(s)
of object's dependences then pass this tree from leafs to roots
and generate right order of "DROP..." commands.
But I guess that executing the "small" version of script 2-3-4... times a little bit easy.

JMdodic,

Dropping and recreating the user can have some messy side effects as well.

All object privileges granted by sys for the user are gone so I am also not happy with this suggestion.


Tycho

I would find out the roles and privileges the user has. Drop the user and recreate the user as exactly as the dropped one.

Quote:

---

Originally posted by tycho
JMdodic,

Dropping and recreating the user can have some messy side effects as well.

All object privileges granted by sys for the user are gone so I am also not happy with this suggestion.


Tycho

---

if you like delete data dictionary go ahead and do so if that makes you happier

Never mess up with data dictionary objects. Even I feel dropping and recreating the user is the safest bet.

stuff that you might want to preserve -- role memebership, passwords, grants etc. -- are all easy things to save should you want to drop the user.

export user/pass file=blah.dump grants=y

drop user errr cascade;

create user errr identified by errr password expire;

import user/pass file=blah.dump grants=y




Hummmmmmm.
Hummmmmmm.

F.