Counting all of the rows in a schema can require code that actually counts the table rows, and it's hard because rows are constantly being added and deleted from the schema. So, how do you count up all of the rows for all tables in a schema? There are two sources of row counts, both of which can become stale:
- Counts as of time last analyzed: The num_rows column in dba_tables, current only to the date-time of the last analyze with dbms_stats.
- Row count at SQL execution time: The "real" current row count, which requires that you actually issue SQL to count the rows in all of the tables (time consuming).
select table_name, to_number( extractvalue( xmltype( dbms_xmlgen.getxml('select count(*) c from '||table_name)) ,'/ROWSET/ROW/C')) ROWS_COUNT FROM USER_TABLES ORDER BY ROWS_COUNT DESC;