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!
Cool, Jeff! I am sure I will steal more scripts from you:-)
Set linesize 100
Set pagesize 0
set head off feedback off verify off termout off Timing off time off
Prompt spool drop_all.log
'drop ' ||object_type||' '||
object_name||decode(object_type,'TABLE',' cascade constraints ')
|| ';' )
Order by object_name
Prompt Spool off
set pagesize 14
Set head on feedback on verify on termout on
Why not just save the create user as a script, seems a lot easier to me.
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.
The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.
Click Here to Expand Forum to Full Width