Oracle - drop multiple table in a single query

Question

I have fifty tables in a database, in that I need only six tables.

How can I delete remaining tables by one single query?

Answer 1

Use something like this, as there is no direct command or way in oracle to do this

begin
  for rec in (select table_name 
              from   all_tables 
              where  table_name like '%ABC_%'
             )
  loop
    execute immediate 'drop table '||rec.table_name;
  end loop;             
end;
/

Answer 2

You can generate a list of DROP TABLE commands with the query below:

SELECT 'DROP TABLE ' || table_name || ';' FROM user_tables;

After that you remove your six tables you want to keep and execute the other commands. Or you add a WHERE table_name NOT IN (...) clause to the query.

Hope it helps.