oracle 如何快速找出没有记录的空表

一、 问题背景

业务方要求找出用户下没有记录的空表，当时把num_rows=0的表导给了他们，后来业务方反馈有一些表count记录数为0，但是没在导出的空表中。检查之后发现这种方法有坑，记录一下检查过程和正确的处理方法。

 

二、 解决方法

还是把解决方法放在最前面，如果是有这种需求，相信也不会希望那么长翻到底才找到方法。

 

法1：count所有表

毫无疑问这是最准确的方法，可以执行以下脚本得到空表。这里对原脚本做了一点小改进，只count num_rows较小或为空的表，count过大的表慢还可能引发性能问题，既然只要空表，count已知的大表完全是浪费时间。

--生成脚本
set echo off heading off feedback off lines 100 pages 0; 
spool tmp.sql 
select 'select ''' || table_name || ''' from ' || table_name || ' having count(*) = 0;' from user_tables where num_rows is null or num_rows<10000; 
spool off

生成的脚本类似

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;

运行脚本即可

--运行脚本
spool result.txt
@tmp.sql
spool off

 

法2：查num_rows=0或num_rows is null的表

测试了几个库应该是准的，如果不放心，还是用第一种方法最安全

select table_name from user_tables where num_rows=0 or num_rows is null;

 

法3：收集统计信息后再查num_rows=0的表

注意如果有大表不要在业务高峰期运行

exec dbms_stats.gather_schema_stats('anbob');

select table_name from user_tables where num_rows=0;

 

处理完需求再回到最开始的问题，为什么导出的空表会比实际少？

三、 为什么导出的空表会比实际少？

• 刚好之前有用expdp导出过这个用户下的数据，查了下导出的日志，结果居然没有搜到这个表。
• 想了一下这个表会不会是在导出空表之后才建的，但检查created发现是前年建的，并不是新表。
• 既然当初导出的是num_rows=0的表，自然也就想到查一下有问题的表num_rows是多少，一查发现是null。

• 所以并不是所有没有记录的表num_rows都是0，还有可能是null，当时没想到还有这个问题，所以导出的空表少了。

 

四、 什么样的表num_rows会为null？

为什么大家都是空表，这个表num_rows会是null？换句话说，什么样的表num_rows会为null？

根据官方文档，这个dba_tables视图的内容来自于统计信息，而带有*的字段必须收集统计信息后才会有值，而num_rows就是带有*的字段。也就是说num_rows为null是因为表没有收集过统计信息。

查一下该表上次收集统计信息的时间，发现果然是空的。

 

参考

https://www.anbob.com/archives/1072.html 

https://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_2105.htm#REFRN20286

https://stackoverflow.com/questions/18924704/how-to-export-empty-tables-in-oracle

https://www.codeproject.com/Questions/282612/am-trying-to-export-empty-table-from-oracle11-2-bu