Delete all table in the schema with a single command
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

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

Hybrid View

  1. #1
    Join Date
    May 2000
    Location
    Delhi
    Posts
    108

    Question Delete all table in the schema with a single command

    Hi All,

    I want to delete all the tables in my schema in a single command / function. I don't have DBA rights for this schema and I have only SQL Plus. I am working on :

    Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production
    PL/SQL Release 9.0.1.0.0 - Production
    CORE 9.0.1.0.0 Production
    TNS for Solaris: Version 9.0.1.0.0 - Production
    NLSRTL Version 9.0.1.0.0 - Production

    Thanks in adv,

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    spool drop.sql
    select 'drop table ' || table_name || ';' from tabs;
    spool off

    @drop

  3. #3
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    Run the following

    Code:
    SET HEADING OFF FEEDBACK OFF PAGES 0
    SPOOL delete_table.sql
    SELECT 'DROP TABLE '||table_name||';'
    FROM user_tables;
    SPOOL OFF
    
    @delete_table
    This is a very simplistic approach and you will probably need to disable any referential constraints.

    HTH
    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!

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    iF you wana drop all objects in a schema, then Drop schema & Recreate it. Any Probs?
    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. #5
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525

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

    Originally posted by sonuji_in
    I want to delete all the tables in my schema
    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!

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Jim :

    I saw that he indeed wrote Tables, but what I guessed, seeing the way he posted, was he wants to flush all of the objects(Tables,Procs,Packages and so)...

    Let him comment on it.

    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"

  7. #7
    Join Date
    May 2000
    Location
    Delhi
    Posts
    108

    Want to delete all objects in a schema

    Thanks for the co-operation and instant reply....

    This procedure worked fine and I was able to delete all the tables :

    spool drop.sql
    select 'drop table ' || table_name || ';' from tabs;
    spool off

    @drop


    but, I want to delete all tbe objects in the schema.

    Thanks,,

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Code:
    
    set termout on
    set head off feedback off verify off termout off
    spool /tmp/drop_all.sql
    Select 
    Rtrim('drop '||object_type||' '||owner||'.'
           ||object_name||decode(object_type,'TABLE',' cascade constraints')
           || ';' )
    from dba_objects
    where owner = upper('&owner')
    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

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    I dunno why you guys wana do circus....isnt this enough?
    Originally posted by abhaysk
    iF you wana drop all objects in a schema, then Drop schema & Recreate it. Any Probs?
    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"

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well many times I cant be bothered to recreate users and grant all the privileges, roles, profiles

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