Search This Blog

Tuesday, May 21, 2013

how to retrieve total row count for each oracle table


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).
oracle ace laurent schneider has a more elegant solution for counting tables, using dbms_xmlgen to store the row counts for multiple tables in a single sql query list::
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;
Post a Comment