Quick way to drop all tables in a schema!
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Quick way to drop all tables in a schema!

Hybrid View

  1. #1
    Join Date
    Jun 2004
    Location
    Bahrain.(916)
    Posts
    3

    Talking Quick way to drop all tables in a schema!

    Hi freiends
    I have a schema with about 100 tables. Is there a single command to drop all tables in the schema rather than having to call DROP TABLE 100 times?

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    No such thing there.. but if possible you can drop the user and recreate it.
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

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

  4. #4
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    write a script to create the sql to do it for you like this..
    Code:
    set echo off termout on pages 0 feedback off
    accept site prompt 'Enter db name: '
    spool run_drop_table1.&site..sql
    select 'drop table '||owner||'.'||table_name||' cascade constraint;'
    from dba_tables where owner in ('insert_owner_here') order by table_name desc;
    spool off
    set echo on termout on pages 24 feedback on 
    spool truncate.lst
    @run_drop_table1.&site..sql
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  5. #5
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Davey and OracleDoc,
    You will be still calling "DROP TABLE" 100 times. See the original post.
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    yeah i know, but it is kinda of one statement.

    Just an alternative because if you drop the user, lose all your rights in the database, thats all

  7. #7
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    Sanjay, why do you always have to be so meticulous Once again you are correct.
    I was just going under the assumption that the orginal poster understood what you said was correct, and that the only way you could do it quickly was through the script.
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  8. #8
    Join Date
    Jan 2001
    Posts
    3,131
    SQL> Drop user Yo_Moma cascade;

    This WILL drop all the schema table with one command.

    So what, I colored outside the lines, kill me!

    MH
    I remember when this place was cool.

  9. #9
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,027
    The other option is to do a dropt tablespace xxx including contents. Assuming that the only only objects owned by the schema are in the table space that you want dropped.

    Therefore if all of the objects are in 1 tablespace and only one tablespace, with no other data. This will meet your requirements.
    I would either drop the user or do dynamic sql to drop the tables.

  10. #10
    Join Date
    Feb 2001
    Posts
    295
    Is that a contest?

    How would you hang an entire production environment with a single command and the hands tied?
    An ounce of action is worth a ton of theory.
    —Friedrich Engels

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