How to drop the objects that a user own?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: How to drop the objects that a user own?

  1. #1
    Join Date
    May 2001
    Posts
    285

    Question

    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!

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    May 2001
    Posts
    285
    Cool, Jeff! I am sure I will steal more scripts from you:-)


  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Code:
    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

  5. #5
    Join Date
    Jan 2001
    Posts
    3,131

    Lightbulb

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

    MH

  6. #6
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    372
    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.
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width