How to drop the objects that a user own?

[elaine3839]

I have an dummy user owns quite a few db objects, and during the development process, I need to drop all objects that this user owns in case there is anything goes wrong which happened quite constantly this afternoon. What I am doing now is 'drop user user_name cascade' which works fine, but I have to recreate the user each time and grant the user proper rights.

So is there any quick and easy way for me to drop all db objects for the user w/o drop the user? Also, does ERwin have the ability to generate the drop script (in accordance with the forward-engineering schema script it generates)?

Thanks a lot!

[marist89]

http://home.adelphia.net/~marist89/s...ipe_schema.htm

[elaine3839]

Cool, Jeff! I am sure I will steal more scripts from you:-)

[pando]

Set linesize 100
Set pagesize 0
set head off feedback off verify off termout off Timing off time off
Spool c:\temp\temp_drop.tmp
Prompt spool drop_all.log
Select Rtrim(
    'drop ' ||object_type||' '||
        object_name||decode(object_type,'TABLE',' cascade constraints ')
        || ';' )
  from user_objects
 Order by object_name
/
Prompt Spool off
Spool off
@c:\temp\temp_drop.tmp
set pagesize 14
Set head on feedback on verify on termout on

[Mr.Hanky]

Why not just save the create user as a script, seems a lot easier to me.

MH

[Ablakios]

I would say drop user user_name cascade and use dynamic sql to recreate your user. But having to deal with a lot in your case I will try jeff's script.