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
  2. CREATE TABLE u_tab_rows(tname VARCHAR2(30),trows NUMBER);

  3. Create a function as follows, to get the number of rows of the given table
  4. 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;

  5. Create a procedure to call the function and insert the results into the table created above
  6. 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

Leave a Comment

Your email address will not be published. Required fields are marked *