oracle 查询以查找所有空表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11134992/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query to find all empty tables
提问by user472625
Considering that I have a Schema named SBST
考虑到我有一个名为 SBST 的架构
I want to find all empty tables list in this SBST Schema. Is there any PL/SQL procedure to find that. I found few. But those were using user tables where I was not able specify the Schema name SBST.
我想在这个 SBST 架构中找到所有空表列表。是否有任何 PL/SQL 程序可以找到它。我发现很少。但是那些正在使用我无法指定架构名称 SBST 的用户表。
I was using this
我正在使用这个
select table_name from dba_tables where owner ='SBST'
having count(*)=0 group by table_name
What's wrong in the above query?
上面的查询有什么问题？
回答by user5243338
You can fire below query to find the list of tables haveing no data
您可以触发以下查询以查找没有数据的表列表
select * from ALL_TABLES
where owner ='SBST'
AND NUM_ROWS = 0;
回答by Alex Poole
Similar to @shareef's answer, but using dynamic SQLto avoid having to create the temporary .sqlfile. You'll need dbms_outputto be visible, e.g. with set serveroutput onin SQL*Plus - don't know about Toad.
类似于@shareef 的回答，但使用动态 SQL来避免创建临时.sql文件。您需要dbms_output可见，例如set serveroutput on在 SQL*Plus 中 - 不了解 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;
/
Using all_tablesseems more useful than dba_tableshere so you know you can select from the tables it lists. I've also included the schema in the fromclause in case there other users have tables with the same name, and so you can still see it if you're connected as a different user - possibly avoiding synonym issues too.
使用all_tables似乎比dba_tables这里更有用，因此您知道您可以从它列出的表格中进行选择。我还在from子句中包含了架构，以防其他用户有同名的表，因此如果您以不同的用户身份连接，您仍然可以看到它 - 也可能避免同义词问题。
Specifically what's wrong with your query... you've got the havingand group byclauses the wrong way around; but it will always return no data anyway because if SBST has any tables then count (*) from dba_tablesmust be non-zero, so the havingalways matches; and if it doesn't then, well, there's no data anyway so there's nothing for the havingto match against. You're counting how many tables there are, not how many rows are in each table.
具体来说，您的查询有什么问题……您的havingandgroup by子句使用错误；但无论如何它总是不会返回任何数据，因为如果 SBST 有任何表，那么它count (*) from dba_tables必须是非零的，所以having总是匹配；如果没有，那么，无论如何都没有数据，所以没有什么having可以匹配的。您正在计算有多少个表，而不是每个表中有多少行。
回答by shareef
the straight forward answer is
直接的答案是
select 'select ''' || table_name || ''' from ' || table_name || '
having count(*) = 0;' from dba_tables where owner='SBST';
EXPLANATION
解释
You can run this... it will just output the table names of those having no rows: assuming you use sqlplus but i used toad to test it and it worked very well set echo off heading off feedback off lines 100 pages 0;
你可以运行这个...它只会输出那些没有行的表名：假设你使用 sqlplus，但我用 toad 来测试它，它运行得很好，设置 echo off 关闭第 100 页 0 行的反馈；
spool tmp.sql
select 'select ''' || table_name || ''' from ' || table_name || '
having count(*) = 0;' from user_tables where owner='SBST';
spool off;
@tmp.sql
If you open the "tmp.sql" file, you'll see for all tables....
如果您打开“tmp.sql”文件，您将看到所有表....
select 'PERSONS' from PERSONS having count(*) = 0;
select 'DEPARTMENT' from DEPARTMENT having count(*)=0;
in your case you want a schema and schema is a user right the above code if you connect with the user SBSTbut if you connect with other then you have to use DBA_TABLESand assign ownerattribute to SBST
在您的情况下，您需要一个架构，并且架构是用户权限以上代码，如果您与用户SBST连接，但如果您与其他用户连接，则必须使用DBA_TABLES并将所有者属性分配给SBST
USER_TABLESis tables which you own ALL_TABLESis tables which own, and tables owner by other users, which you have been granted excplicit access to DBA_TABLESis all tables in the database
USER_TABLES是您拥有的表ALL_TABLES是您拥有的表，其他用户拥有的表，您已被授予对 DBA_TABLES 的显式访问权限是数据库中的所有表
like this
像这样
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 dba_tables where owner='SBST';
spool off;
@tmp.sql
All three are views of the underlying SYS tables, but the USER_ and ALL_ views joing in your username/security info to limit the results
这三个都是底层 SYS 表的视图，但是 USER_ 和 ALL_ 视图加入了您的用户名/安全信息以限制结果
**SUMMARY **
**概括 **
PLEASE JUST RUN THIS QUERY
请运行此查询
select 'select ''' || table_name || ''' from ' || table_name || '
having count(*) = 0;' from dba_tables where owner='SBST';
回答by justMe
If table stats are up to date then you could use:
如果表统计信息是最新的，那么您可以使用：
SELECT TABLE_NAME
FROM ALL_TAB_STATISTICS
WHERE (OWNER = 'ME')
AND (NUM_ROWS = 0);
