ORACLE : GET RECORD COUNTS OF ALL TABLES IN A SCHEMA

METHOD-1:

Below query will give a number of rows for the required tables but these are not accurate until we ANALYZE(gather stats) the tables. So we can not depend on ALL_TABLES system table for getting accurate rows count.

 SELECT table_name,num_rows FROM all_tables WHERE owner = '<Schema>'; 

So we can use below set of statements to find the count of rows of all the tables at once and store them in one permanent table for analysis purpose.

CREATE TABLE stats AS SELECT table_name, num_rows FROM all_tables WHERE 1=2; 
DESC stats;

desc-count-tables
Run the below PL/SQL code:

DECLARE
val integer;
BEGIN
for i in (SELECT table_name FROM all_tables WHERE owner = '<Schema>')
LOOP
EXECUTE IMMEDIATE 'SELECT count(*) from ' || i.table_name INTO val;
INSERT INTO <Schema>.stats VALUES (i.table_name,val);
END LOOP;
END;
/

You should get ‘PL/SQL procedure successfully completed.’ message from the editor.

Now the table names and rows count is stored in our newly created table.

SELECT * FROM stats;

oracle-final-count-of-all-tables

 

METHOD-2:

SELECT table_name,
TO_NUMBER(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) cnt from '||owner||'.'||table_name)),'/ROWSET/ROW/CNT')) as count
FROM all_tables
WHERE owner = '<Schema>'

oracle-xml-path-record-count

Comments

comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: