Oracle - drop multiple table in a single query

To expand on the answer, for Oracle 10 versions and later, dropped tables are not deleted permanently, but moved to recycled bin. To truly delete tables need to add optional parameter PURGE.

Expanding on the accepted answer :

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

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.

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;
/