DROP all tables starting with "EXT_" in Oracle SQL

Question

I know this question may ask many times but I could not find one line SQL statement. I remember I did it before but now I could not remember how I did

I want to drop all tables whose name starts with "EXT_". Is it possibile to make it happen with one line SQL statement.

Answer 1

You could use a short anonymous block to do this.

BEGIN
  FOR c IN ( SELECT table_name FROM user_tables WHERE table_name LIKE 'EXT_%' )
  LOOP
    EXECUTE IMMEDIATE 'DROP TABLE ' || c.table_name;
  END LOOP;
END;