oracle 获取模式中所有表的计数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10704808/
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
Get counts of all tables in a schema
提问by Ram
I am trying to get the record counts of all tables in a schema. I am having trouble writing the PL/SQL. Here is what I have done so far, but I am getting errors. Please suggest any changes:
我正在尝试获取模式中所有表的记录数。我在编写 PL/SQL 时遇到问题。这是我到目前为止所做的，但我遇到了错误。请提出任何更改：
DECLARE
v_owner varchar2(40);
v_table_name varchar2(40);
cursor get_tables is
select distinct table_name,user
from user_tables
where lower(user) = 'SCHEMA_NAME';
begin
open get_tables;
fetch get_tables into v_table_name,v_owner;
INSERT INTO STATS_TABLE(TABLE_NAME,SCHEMA_NAME,RECORD_COUNT,CREATED)
SELECT v_table_name,v_owner,COUNT(*),TO_DATE(SYSDATE,'DD-MON-YY') FROM v_table_name;
CLOSE get_tables;
END;
采纳答案by Tony Andrews
This should do it:
这应该这样做：
declare
v_count integer;
begin
for r in (select table_name, owner from all_tables
where owner = 'SCHEMA_NAME')
loop
execute immediate 'select count(*) from ' || r.table_name
into v_count;
INSERT INTO STATS_TABLE(TABLE_NAME,SCHEMA_NAME,RECORD_COUNT,CREATED)
VALUES (r.table_name,r.owner,v_count,SYSDATE);
end loop;
end;
I removed various bugs from your code.
我从您的代码中删除了各种错误。
回答by a_horse_with_no_name
This can be done with a single statement and some XML magic:
这可以通过一条语句和一些 XML 魔法来完成：
select table_name,
to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from '||owner||'.'||table_name)),'/ROWSET/ROW/C')) as count
from all_tables
where owner = 'FOOBAR'
回答by Jon Heller
select owner, table_name, num_rows, sample_size, last_analyzed from all_tables;
This is the fastest way to retrieve the row counts but there are a few important caveats:
这是检索行数的最快方法，但有一些重要的警告：
- NUM_ROWS is only 100% accurate if statistics were gathered in 11g and above with
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE(the default), or in earlier versions withESTIMATE_PERCENT => 100. See this postfor an explanation of how the AUTO_SAMPLE_SIZE algorithm works in 11g. - Results were generated as of
LAST_ANALYZED, the current results may be different.
- 如果在 11g 及更高版本中使用
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE（默认）收集统计信息，或者在使用ESTIMATE_PERCENT => 100. 有关AUTO_SAMPLE_SIZE 算法如何在 11g 中工作的说明，请参阅此帖子。 - 结果是从 生成的
LAST_ANALYZED，当前的结果可能会有所不同。
回答by J?rg
If you want simple SQL for Oracle (e.g. have XE with no XmlGen) go for a simple 2-step:
如果您想要用于 Oracle 的简单 SQL（例如，有没有 XmlGen 的 XE），请执行简单的 2 步：
select ('(SELECT ''' || table_name || ''' as Tablename,COUNT(*) FROM "' || table_name || '") UNION') from USER_TABLES;
Copy the entire result and replace the last UNION with a semi-colon (';'). Then as the 2nd step execute the resulting SQL.
复制整个结果并用分号 (';') 替换最后一个 UNION。然后作为第二步执行生成的 SQL。
回答by michael jie
Get counts of all tables in a schema and order by desc
获取模式中所有表的计数并按 desc 排序
select 'with tmp(table_name, row_number) as (' from dual
union all
select 'select '''||table_name||''',count(*) from '||table_name||' union ' from USER_TABLES
union all
select 'select '''',0 from dual) select table_name,row_number from tmp order by row_number desc ;' from dual;
Copy the entire result and execute
复制整个结果并执行
回答by arturro
You have to use execute immediate (dynamic sql).
您必须使用立即执行（动态 sql）。
DECLARE
v_owner varchar2(40);
v_table_name varchar2(40);
cursor get_tables is
select distinct table_name,user
from user_tables
where lower(user) = 'schema_name';
begin
open get_tables;
loop
fetch get_tables into v_table_name,v_owner;
EXIT WHEN get_tables%NOTFOUND;
execute immediate 'INSERT INTO STATS_TABLE(TABLE_NAME,SCHEMA_NAME,RECORD_COUNT,CREATED)
SELECT ''' || v_table_name || ''' , ''' || v_owner ||''',COUNT(*),TO_DATE(SYSDATE,''DD-MON-YY'') FROM ' || v_table_name;
end loop;
CLOSE get_tables;
END;
