oracle 找出没有记录的空表(zero rows tables)

oracle 11g以前，建立空表也会分配段占用磁盘空间，如果查出库中所有没有记录的空表呢？呵今天无意间发现一怪语法

anbob@ANBOB> set echo off heading off feedback off lines 100 pages 0; 
anbob@ANBOB> spool tmp.sql 
anbob@ANBOB> select 'select ''' || table_name || ''' from ' || table_name || ' having count(*) = 0;' from user_tables; 
anbob@anbob>spool off
查看一下生成的tmp.sql，
sql>@tmp.sql
select 'TESTEXPDP' from TESTEXPDP having count(*) = 0;
select 'TESTCOMP' from TESTCOMP having count(*) = 0;
select 'TESTTAB' from TESTTAB having count(*) = 0;
select 'TESTTAB_IME' from TESTTAB_IME having count(*) = 0;
select 'TESTTAB_DEF' from TESTTAB_DEF having count(*) = 0;
....

看到的是什么？呵呵，就是没有记录的表名
当然不如果不费时间也可以用统计信息数据字典
sql> exec dbms_stats.gather_schema_stats('anbob');
PL/SQL procedure successfully completed.
sql> conn anbob/anbob
Connected.
anbob@ORCL> select table_name from user_tables where num_rows=0;