September 20th, 2013 
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;
