How to generate a sql script that drops all tables starting with a certain prefix

In some circumstances, you may want to drop all tables in a schema that start with certain prefix. Say for example you like drop all tables starting with the prefix 'TMP_'.

You can execute:

BEGIN
  FOR t IN ( SELECT table_name FROM user_tables WHERE table_name LIKE 'TMP_%' )
  LOOP
    EXECUTE IMMEDIATE 'DROP TABLE ' || t.table_name || ' CASCADE CONSTRAINTS';
  END LOOP;
END;

This block finds and drops all tables whose name start with 'TMP_'.

Another way is to first generate a list of drop statements, save the list as a sql script and finally execute it. Personally I like this way better since it lets me to see and be sure exactly what is going to be removed.

This block generates a list of drop statements for all tables in your schema starting with 'TMP_' :

select 'drop table ' || table_name || ' cascade constraints' || ';'
from   user_tables
where  table_name like 'TMP_%';

You can then take this generated list, save it as a sql script and execute it as you wish.