首页 » ORACLE 9i-23ai » oracle 找出没有记录的空表(zero rows tables)

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;

打赏

目前这篇文章有1条评论(Rss)评论关闭。

  1. Maynard Teichmann | #1
    2011-12-21 at 15:32

    Some buyers think about this to be much more hassle-free than touring to a local community drugstore, in the identical way as ordering items online somewhat than heading to a store.

上一篇：

下一篇：