Row count for all tables in a schema

Row count of the tables in a schema can be fetched from USER_TABLES.

Query Output Method: Run the DBMS_STATS.gather_schema_stats package/procedure to gather statistics for all the objects in the schema and then query the USER_TABLES for the NUM_ROWS column to fetch the row count. This is one of the well known methods.

SQL> select table_name, nvl(num_rows,0) from user_tables order by 2 desc;

XML Output Method : In this method we use the DBMS_XMLGEN written by Mathias TITLE: Dynamic in-line queries.

SQL> select table_name
,to_number(extractvalue(xmltype(
dbms_xmlgen.getxml(
'select count(*) c from '||table_name))
,'/ROWSET/ROW/C')) count
from user_tables;

Notes:
The inner query calls the function dbms_xmlgen to generate the output in xml format, to return one row and one value for every row fetched by the outer query.
XMLTYPE: The XMLTYPE wrapper function converts the xml output to xmlobject so XPATH functions can be used. In this case xmltype will return xmls data as

<?xml version="1.0"?>
<ROWSET>
<ROW>
 <C>200</C>
</ROW>
</ROWSET>

EXTRACTVALUE The EXTRACTVALUE is an xml function which takes arguments of XMLType and an XPath expression and returns a scalar value. In this case xpath is '/ROWSET/ROW/C'.
TO_NUMBER: function to convert to number