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