Использовать DBMS_SQL.

есть представление ALL_TABLES из которого можно выбрать название таблиц по маске select ... where table_name like 'INZ%' and owner = 'USER1'
а дальше в процедуре описываете курсор, и по fetch , формируете динамический SQL, который успешно выполняете.

Вот Вам пример DBMS_SQL.

Rem Copyright (c) 1995, 1996, 1997 by Oracle Corp. All Rights Reserved.
Rem
Rem NAME
Rem owadsyn.sql - OWA Drop public SYNonyms
Rem PURPOSE
Rem Drop the public OWA synonyms used by the PL/SQL
Rem gateway.
Rem NOTES
Rem This script should be run as sys.
Rem history
Rem pkapasi 09/07/00 - Ignore drop errors for SQL*Plus and svrmgrl
Rem rdecker 07/21/00 - split off from owacomm.sql
Rem

DECLARE
-- procedure executes a DDL and ignores errors if any.
PROCEDURE execute_ddl(ddl_statement VARCHAR2) IS
ddl_cursor INTEGER;
BEGIN
-- try to execute DDL
ddl_cursor := dbms_sql.open_cursor;

-- issue the DDL statement
dbms_sql.parse (ddl_cursor, ddl_statement, dbms_sql.native);
dbms_sql.close_cursor (ddl_cursor);
EXCEPTION
-- ignore exceptions
when others then
if (dbms_sql.is_open(ddl_cursor)) then
dbms_sql.close_cursor(ddl_cursor);
end if;
END;

BEGIN
execute_ddl ('drop public synonym OWA_CUSTOM');
execute_ddl ('drop public synonym OWA_GLOBAL');
execute_ddl ('drop public synonym OWA');
execute_ddl ('drop public synonym HTF');
execute_ddl ('drop public synonym HTP');
execute_ddl ('drop public synonym OWA_COOKIE');
execute_ddl ('drop public synonym OWA_IMAGE');
execute_ddl ('drop public synonym OWA_OPT_LOCK');
execute_ddl ('drop public synonym OWA_PATTERN');
execute_ddl ('drop public synonym OWA_SEC');
execute_ddl ('drop public synonym OWA_TEXT');
execute_ddl ('drop public synonym OWA_UTIL');
execute_ddl ('drop public synonym OWA_INIT');
execute_ddl ('drop public synonym OWA_CACHE');
execute_ddl ('drop public synonym WPG_DOCLOAD');
END;
/



Или если у Вас свежая версия оракла:

declare
cursor c is select table_name from all_tables
where owner = '...' and
like 'MASK%';
begin
for c_rec in c loop
execute immediate 'drop table "' || c_rec.table_name || '"' ;
end loop;
end;

Написать процедуру и подвесить в джоб.

Для того чтобы видеть dba_tables , нужно иметь привелегии на это представление.
Правило второе.
Не нужно давать лишние привелегии это бывет опасно.
to softbuilder@inbox.ru