Get counts of all tables in a schema

问题:

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:

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;

回答1:

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.

回答2:

This can be done with a single statement and some XML magic:

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\'

回答3:

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:

1. 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 with ESTIMATE_PERCENT => 100. See this post for an explanation of how the AUTO_SAMPLE_SIZE algorithm works in 11g.
2. Results were generated as of LAST_ANALYZED, the current results may be different.

回答4:

If you want simple SQL for Oracle (e.g. have XE with no XmlGen) go for a simple 2-step:

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.

回答5:

You have to use execute immediate (dynamic 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; 

回答6:

This query gives the counts of all tables in a schema:

select sum(num_rows) from all_tables where owner=\'Schema_name\';