How to drop all tables that are empty

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