Here's an easy way to drop all the tables that have no data in them. Save this to a file called drop_tab.sql and run by using: @drop_tab After this has run, a file named dr_tab.sql will have been created. You can then review this file and run if you would like to drop these empty tables. -- drop_tab.sql -- Drop all the empty tables for a schema user set feed off set echo off set verify off set lines 80 set serveroutput on size 50000 set pages 0 set term off spool dr_temp.sql Declare cursor drop_cursor is select table_name from user_tables; begin for dc in drop_cursor LOOP dbms_output.put_line('Declare'); dbms_output.put_line(chr(9)||'rec_count number;'); dbms_output.put_line('Begin'); dbms_output.put_line('select count(*) into rec_count from '||dc.table_name||';'); dbms_output.put_line('If rec_count = 0 then'); dbms_output.put_line(chr(9)||'dbms_output.put_line(''drop table '||dc.table_name||' cascade constraints;'');'); dbms_output.put_line('end if;'); dbms_output.put_line('end;'); dbms_output.put_line('/'); end loop; end; / spool off spool dr_tab.sql @dr_temp spool off set term on set lines 132 set pages 66 prompt "dr_tab.sql has been created" prompt "Use this to drop empty tables..." set verify on set feed on