SQL query to fetch all user defined tables and their Row count.

If you want to find how many user defined tables exists in the database and also how many Rows each table contains, fire the below query.

SELECT TABLE_NAME,
    TO_NUMBER(EXTRACTVALUE(XMLTYPE(DBMS_XMLGEN.GETXML('SELECT                     COUNT(*) C FROM '
    ||OWNER ||'.' ||TABLE_NAME)),'/ROWSET/ROW/C')) TABLE_ROWS
FROM ALL_TABLES
WHERE OWNER=SchemaName;

Note: Here SchemaName should be your schema name in single quotes.