[转载]---Oracle找出没有记录的空表(zero row tables)

Oracle11g以前，建立空表也会分配段占用磁盘空间，如何查出库中所有没有记录的空表呢？

SQL> set echo off heading off feedback off lines 100 pages 0;
SQL> spool tmp.sql
SQL> select 'select ''' || table_name || ''' from ' || table_name || ' having count(*) = 0;' from user_tables;
select 'EMP' from EMP having count(*) = 0;
select 'DEPT' from DEPT having count(*) = 0;
select 'TESTNEW_IME' from TESTNEW_IME having count(*) = 0;
select 'TESTNEW_DEF' from TESTNEW_DEF having count(*) = 0;
select 'TESTNEW_DEFS' from TESTNEW_DEFS having count(*) = 0;
select 'JACK' from JACK having count(*) = 0;
select 'TESTNEW' from TESTNEW having count(*) = 0;
SQL> spool off
SQL> @tmp.sql
TESTNEW_IME
TESTNEW_DEF
TESTNEW_DEFS
JACK
TESTNEW

[oracle@rhel5 ~]$ more tmp.sql 
SQL> select 'select ''' || table_name || ''' from ' || table_name || ' having count(*) = 0;' from user_tables;
select 'EMP' from EMP having count(*) = 0;                                                          
select 'DEPT' from DEPT having count(*) = 0;                                                        
select 'TESTNEW_IME' from TESTNEW_IME having count(*) = 0;                                          
select 'TESTNEW_DEF' from TESTNEW_DEF having count(*) = 0;                                          
select 'TESTNEW_DEFS' from TESTNEW_DEFS having count(*) = 0;                                        
select 'JACK' from JACK having count(*) = 0;                                                        
select 'TESTNEW' from TESTNEW having count(*) = 0;                                                  
SQL> spool off

在上面执行tmp.sql后可以看到没有记录的表名。
当然也可以使用统计信息数据字典：

SQL> exec dbms_stats.gather_schema_stats('jack');
 
PL/SQL procedure successfully completed
 
SQL> select table_name from user_tables where num_rows=0;
 
TABLE_NAME
------------------------------
TESTNEW_IME
TESTNEW_DEF
TESTNEW_DEFS
JACK
TESTNEW