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;
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.
Get counts of all tables in a schema and order by 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
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;
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:
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.LAST_ANALYZED
, the current results may be different.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'
Source: Stackoverflow.com