Get Row Counts for Each Table in Oracle Database by using XMLQUERY

get_row_count_xml.sql

SQL> set autotrace traceonly statistics
 
SQL>select 
  owner, 
  table_name, 
  XMLCAST(
    XMLQUERY(
      '/ROWSET/ROW/NUMROWS' PASSING DBMS_XMLGEN.GETXMLTYPE(
        'select  count(*)  NUMROWS
     from ' || '"' ||  OWNER || '". "' || TABLE_NAME || '"'
      ) RETURNING CONTENT
    ) AS NUMBER
  ) ROW_COUNT 
FROM 
  DBA_TABLES T 
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
----------------------------------------------------------
	140  recursive calls
	  0  db block gets
     513727  consistent gets
     493203  physical reads
	  0  redo size
       1468  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