Oracle row count for all tables in schema

Count each number of table rows in specific schema.

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:

SQL> set pages 999;

SQL> col count format 999,999,999;

SQL> spool /home/oracle/row_count.txt

SQL> 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
   table_name;

SQL> spool off;

Output like this

TABLE_NAME                      COUNT
------------------------------ ------
DEPT                                4
EMP                                14
BONUS                               0
SALGRADE                            5