DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: Delete all table in the schema with a single command

  1. #11
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    User creation/grant/assign_profile script wont do?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  2. #12
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    so whats the difference between writing a script to drop all objects and writing a script to create users then do all the grants, roles and profiles?

    A script to create a user is specific each time, a script to drop objects can be used over and over again

  3. #13
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Abhay, hasn't the original poster explicitely specified he doesn't have DBA privileges in his very first message? So how would he drop schema?

    It has to be done object by object, no other way around, not with the description of the problem he provided. Ther's no magic "DROP ALL MY OBJECTS" command as he was hoping. Of course, using SQL to generate scripts to drop all objects is trivial and will speed up the dropping extensively. Jim has provided him the sample for the tables - the remaining stuff for all other type of objects should be easy.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #14
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by davey23uk
    A script to create a user is specific each time, a script to drop objects can be used over and over again
    Specifc? how...pass pram (user_name) and make ur script genric one.

    But yes I didnt notice, that He said he didnt have DBA Privilage as Jurij pointed me...

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #15
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    A few changes to Pando's script make it generic and without the DBA dependency:

    Code:
    set termout on
    set head off feedback off verify off termout off
    spool /tmp/drop_all.sql
    Select 
      Rtrim('drop '||object_type||' '
        ||object_name||decode(object_type,'TABLE',' cascade constraints')
        || ';' )
    from user_objects
    order by object_name
    /
    spool off
    
    set head on feedback on verify on
    
    spool /tmp/drop_all.log
    
    set echo on
    @/tmp/drop_all.sql
    spool off
    
    set echo off
    
    set pagesize 14 termout on
    But it does not detract from the fact that the original post requested how to remove all tables and not all objects. Unless the poster is clear in thier requirements than confusion like this is bound to occur.

    Regards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

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