Jijo K Jose

To drop more than one tables having a matching criteria then we should use a PL SQL statement, in oracle there is a table called USER_OBJECTS having name of all the tables for that particular user.

DROP TABLE is a DDL statement so that we need EXECUTE IMMEDIATE privilege for dropping table from a script, more easy way to drop table is to generate DROP TABLE command using a script and run that command.

The following PL SQL script generate drop table command for all tables having table name contains  word 'test'

SET SERVEROUTPUT ON;

DECLARE
  object_name varchar2(100);
  cursor cur1 is SELECT object_name FROM user_objects WHERE object_type='TABLE' AND object_name LIKE '%test%';
BEGIN

  open cur1;
  loop
        fetch cur1 into object_name;
        exit when cur1%notfound;
      dbms_output.put_line('DROP TABLE ' || object_name || ';' );
    end loop;
    close cur1;

END ;

Output

anonymous block completed

DROP TABLE USER_TEST;

DROP TABLE USER_TEST_VALUE;

DROP TABLE TEST_VALUE;

To generate drop command for triggers and functions just change the OBJECT_TYPE='TABLE' statement into OBJECT_TYPE='TRIGGER' or OBJECT_TYPE='FUNCTION'  or OBJECT_TYPE='INDEX' etc.