ORACLE : GET RECORD COUNTS OF ALL TABLES IN A SCHEMA

METHOD-1:

The 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 the 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; 

Note: you can watch the same content with examples in the below video:

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;

Read also: DIFFERENCE BETWEEN ALL_TAB_COLS AND ALL_TAB_COLUMNS IN ORACLE

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

Want to copy/download the code snippets used in this post? Download them from here.

Read also: SQL INTERVIEW QUESTION – BINARY TREE NODES QUESTION AND SOLUTION WITH QUERY

Comments

comments