Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2009
    Posts
    7

    Unanswered: Drop few hundred table

    Dear all,

    If there any statement to drop few hundred table in one time?

    Thanks.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    No, unless you drop a user (who owns those tables).

    Otherwise, write SQL which will write SQL; something like
    Code:
    spool drop.sql
    
    select 'drop table ' || tname ||' cascade constraints;' from tab;
    
    spool off:
    It will create a table which will contain DROP TABLE statements; edit it a little bit (remove unnecessary lines) and you might do the job.

  3. #3
    Join Date
    Apr 2009
    Posts
    7
    Quote Originally Posted by Littlefoot
    No, unless you drop a user (who owns those tables).

    Otherwise, write SQL which will write SQL; something like
    Code:
    spool drop.sql
    
    select 'drop table ' || tname ||' cascade constraints;' from tab;
    
    spool off:
    It will create a table which will contain DROP TABLE statements; edit it a little bit (remove unnecessary lines) and you might do the job.
    Dear Littlefoot,

    Thank for the prompt reply

    If i drop the user, meaning that i will delete all the data inside the table, same as the drop table?
    Code:
    select 'drop table ' || tname ||' cascade constraints;' from tab;
    for the tname --> i need to key in each of the table name ? or what?

    Thanks.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    No; just run this piece of code in SQL*Plus. Don't run the script, though, before you are sure what you are doing (backup (or export - at least) would be a good idea before dropping tables).

    Dropping a table drops its contents, of course. Dropping a user will also drop all its objects.

    As it seems that you aren't quite familiar with the subject, perhaps you should first educate yourself (documentation is available at the Oracle Technology Network). Also, if you have a senior colleague, chat with him/her a little bit so that you'll get an idea of what you should do, how, what is the result and its consequences.

  5. #5
    Join Date
    Apr 2009
    Posts
    2
    You could use a database scripting tool like dbConstructor to generate the drop tables command, where you can even select which tables are to be dropped and it created the drop tables in reverse dependency order (referential integrity) so that all the tables are dropped on the first run of the script.

  6. #6
    Join Date
    Apr 2009
    Posts
    7
    Quote Originally Posted by rallen1008
    You could use a database scripting tool like dbConstructor to generate the drop tables command, where you can even select which tables are to be dropped and it created the drop tables in reverse dependency order (referential integrity) so that all the tables are dropped on the first run of the script.
    Thanks, i will try it out

Posting Permissions

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