Drop all tables sql developer

Question

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?

Answer 1

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

Answer 2

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;