get tables and their number of rows in Oracle Database Schema

Simplest way is to use the data dictionaries provided by Oracle. And you can depend on the results if the schema is up to date with the statistics.

Use the following query to get the table list and their number of rows (records).

SELECT table_name, num_rows FROM user_tables;

OR

SELECT table_name, num_rows FROM all_tables WHERE owner=’SCOTT’;

The WHERE clause in the 2nd query can be ignored in case the requirement is for all the schemas in the database.

Another way is writing a SQL query to fetch the count in hard way.
Follow the steps below

1. Create a table to load the details

CREATE TABLE u_tab_rows(tname VARCHAR2(30),trows NUMBER);

2. Create a function as follows, to get the number of rows of the given table

CREATE OR REPALCE FUNCTION fn_get_rows( p_tname in varchar2 ) RETURN NUMBER
AS
    l_columnValue    NUMBER DEFAULT NULL;
BEGIN
    EXECUTE IMMEDIATE
       ‘SELECT COUNT(*)
          FROM’ || p_tname INTO l_columnValue;

    RETURN l_columnValue;
END;

3. Create a procedure to call the function and insert the results into the table created above

CREATE OR REPALCE PROCEDURE pr_get_rows AS
BEGIN
    INSERT INTO u_tab_rows
    SELECT TABLE_NAME, fn_get_rows(TABLE_NAME) from user_tables WHERE table_name <> ‘U_TAB_ROWS’ –to exclude the table created above
    AND table_name NOT LIKE ‘SYS%’ AND table_name NOT LIKE ‘%$%’ — to exclude the SYS and DBA tables
    ORDER BY TABLE_NAME;
    COMMIT;
END;

I am here posting the improved version of the code found @ Ask Tom