Query to get record count of all tables in a schema

Use this query to get the record count of all tables in a schema.

select

table_name,
to_number(
extractvalue(
xmltype(
dbms_xmlgen.getxml('select count(*) c from '||table_name)
),'/ROWSET/ROW/C')) count
from user_tables order by 1

The output is like

table_name count
----------- --------
DEPT 4
EMP 14


Courtesy: http://laurentschneider.com/wordpress/2007/04/
how-do-i-store-the-counts-of-all-tables.html

14 comments :

  1. 1 select
    2 table_name,
    3 to_number(
    4 extractvalue(
    5 xmltype(
    6 dbms_xmlgen.getxml('select count(*) c from '||table_name)),'/ROWSET/ROW/C')
    ) count
    7* from user_tables order by 1
    SQL> /
    dbms_xmlgen.getxml('select count(*) c from '||table_name)),'/ROWSET/ROW/C')) cou
    nt
    *
    ERROR at line 6:
    ORA-19202: Error occurred in XML processing
    ORA-00911: invalid character
    ORA-06512: at "SYS.DBMS_XMLGEN", line 176
    ORA-06512: at line 1

    Not so brilliant. Using Oracle 10.2.0.2.

    ReplyDelete
  2. 1 select
    2 table_name,
    3 to_number(
    4 extractvalue(
    5 xmltype(
    6 dbms_xmlgen.getxml('select count(*) c from '||table_name)),'/ROWSET/ROW/C')
    ) count
    7* from user_tables order by 1
    SQL> /
    dbms_xmlgen.getxml('select count(*) c from '||table_name)),'/ROWSET/ROW/C')) cou
    nt
    *
    ERROR at line 6:
    ORA-19202: Error occurred in XML processing
    ORA-00911: invalid character
    ORA-06512: at "SYS.DBMS_XMLGEN", line 176
    ORA-06512: at line 1

    Not so brilliant. Using Oracle 10.2.0.2.

    ReplyDelete
  3. Have you given execute permission to dbms_xmlgen to your user. By default it must be there. But check any way.

    ReplyDelete
  4. I've to say regarding this -

    Query to get record count of all tables in a schema

    Perhaps this has been shown first in otn forum by michael (Now his id is shown as michael2 in the new updated forum).

    ReplyDelete
  5. Anantha:
    This works great in Oracle9, although does not work in Oracle8.
    Returns Error saying extractvalue is invalid. Any ideas???

    ReplyDelete
  6. Thank you - worked for me w Oracle 11g

    ReplyDelete
  7. Thanks Anantha. It's Cool

    ReplyDelete
  8. select table_name, num_rows from user_tables

    ReplyDelete
  9. Excellent thought!!! I used this logic to get record count for all the tableNames from a config table.

    ReplyDelete
  10. SELECT owner,
    table_name,
    TO_NUMBER (
    EXTRACTVALUE (
    xmltype (
    DBMS_XMLGEN.
    getxml (
    'select nvl(count(*),0) c from ' || owner || '.' || table_name)),
    '/ROWSET/ROW/C'))
    COUNT
    FROM dba_tables
    where owner='FNSONLD'
    ORDER BY 1

    ReplyDelete
  11. You could have done it in one line.

    select sum(num_rows) from user_tables;

    ReplyDelete
  12. Very very brilliant! Thanks a lot!

    ReplyDelete