oracle - 查询以查找所有空表

考虑到我有一个名为SBST的架构

我想在此SBST架构中找到所有空表列表。是否有任何PL/SQL过程可以找到这一点。我发现很少。但是那些使用用户表的用户无法指定模式名称SBST。

我在用这个

select table_name from dba_tables where owner ='SBST'
having count(*)=0  group by table_name

上面的查询有什么问题？

最佳答案

与@shareef的答案类似，但是使用dynamic SQL避免创建临时的.sql文件。您需要 dbms_output 可见，例如在SQL * Plus中使用 set serveroutput on -不了解Toad。

declare
    cursor c(p_schema varchar2) is
        select 'select ''' || table_name || ''' from ' ||
            p_schema ||'.' || table_name || ' where rownum < 2 ' ||
            ' having count(*) = 0' as query
        from all_tables
        where owner = p_schema
        order by table_name;
    l_table all_tables.table_name%TYPE;
begin
    for r in c('SBST') loop
        begin
            execute immediate r.query into l_table;
        exception
            when no_data_found then continue;
        end;

        dbms_output.put_line(l_table);
    end loop;
end;
/

在这里使用all_tables似乎比dba_tables更有用，因此您可以从列出的表中进行选择。如果其他用户具有相同名称的表，我还将Schema包含在from子句中，因此，如果您以其他用户身份连接，您仍然可以看到它-可能也避免了同义词问题。

具体来说，您的查询出了什么问题……having和group by子句的处理方式错误。但是它总不会返回任何数据，因为如果SBST有任何表，那么count (*) from dba_tables必须为非零值，因此having始终匹配；如果不是，那么就没有数据了，因此having就没有匹配的内容。您是在计算有多少个表，而不是每个表中有多少行。