Get Row Counts for Each Table in Oracle Database

get_row_counts.sql

SQL> set autotrace traceonly statistics
 
SQL> WITH function get_rows(
  p_owner in varchar2, p_tablename in varchar2
) return number as result_rowcount number default NULL;
begin execute immediate 'select count(*)
from ' || '"' || p_owner || '"."' || p_tablename || '"' INTO result_rowcount;
return result_rowcount;
end;
select 
  owner, 
  table_name, 
  get_rows(owner, table_name) cnt 
from 
  dba_tables 
where 
  owner IN ('HR', 'SH', 'CO') 
AND TABLE_NAME NOT LIKE 'MLOG$\_%' ESCAPE '\' 
  AND NVL(IOT_TYPE, 'NOT_IOT') NOT IN ('IOT_OVERFLOW', 'IOT_MAPPING') 
  AND TEMPORARY = 'N' 
  AND NESTED = 'NO' 
  AND SECONDARY = 'N' 
  AND EXTERNAL = 'NO' 
  AND HYBRID = 'NO' ;


Statistics
----------------------------------------------------------
	 28  recursive calls
	  0  db block gets
     506704  consistent gets
     493409  physical reads
	  0  redo size
       1550  bytes sent via SQL*Net to client
	133  bytes received via SQL*Net from client
	  3  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	 28  rows processed