16

I have tables that names start with "TBL_*", but I can not drop all of them by single command.

how can I drop this tables?

E A
  • 995
  • 1
  • 10
  • 24

2 Answers2

23

You can write a script, specifically, loop, in which you select Table_name from user_tables and iterate this loop , and use "execute immediate" command to drop them.

But I would suggest this - in sql tool do

  select 'drop table ' || table_name || ';' from user_tables where table_name like 'TBL_%'

Then you copy output of this query and paste into your sql editor, and execute. Remember, if sql+ is your editor, if you paste them all, they will start execute. May be you want to use notepad to review and edit it first.

But you can't just drop more than one table in one single command. Check this link for other options associated with drop table http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9003.htm

T.S.
  • 18,195
  • 11
  • 58
  • 78
15
BEGIN

  --Bye Tables!
  FOR i IN (SELECT ut.table_name
              FROM USER_TABLES ut) LOOP
    EXECUTE IMMEDIATE 'drop table '|| i.table_name ||' CASCADE CONSTRAINTS ';
  END LOOP;

END;
Martin Tournoij
  • 26,737
  • 24
  • 105
  • 146
Frank
  • 159
  • 1
  • 2
  • I described this technique in my original answer >3 years ago. And I pointed that it can be dangerous sometimes when working with data http://stackoverflow.com/a/18030453/1704458 – T.S. Mar 14 '17 at 20:28
  • 2
    This gets a downvote, because the user *specifically asked* for only tables starting with "TBL_" - this would delete all! – theMayer Jan 24 '18 at 19:39
  • I am giving vote up because it removes tables without fk complaints unlike the post marked as answered. Shame for oracle to have no such functionality in the app. I often use migrator and drop all tables when debbugin it. – ADM-IT Jun 04 '20 at 15:06