Oracle Database schema tables rows count

 We do come across a requirement or want to know count of each and every table in a particular schema or group of schemas. 

what is simplest way of doing this?.  do we need query each and every table ?.. not required as tables are used for some other DMLs .. . we can create a job or run whenever we want to know the count of each and every table in a particular schema. The code given below can be modified to suit to user requirement. 

There are two ways, run code and wait for output or simply run a job that inserts a record in a table, query stats table to see the count . 

Result table can be truncated or measured to see how each table growth is in terms of rows count..  similar option can be applied to find schema size , perhaps once is a day or run once in a week and compare the database growth .. capacity planning.. etc.. many uses. .. 

 

 Create a table:

Create table STATS_TABLE

(TABLE_NAME VARCHAR2(100) not null,

SCHEMA_NAME VARCHAR2(100) not null,

RECORD_COUNT NUMBER(12),

CREATED DATE);

Option 1:

set serveroutput on;

DECLARE

    v_count        INTEGER;

    l_owner_name   VARCHAR2(30) := 'PRODADMIN';

    l_table_name   varchar2(100);

    l_sql_qry     VARCHAR2 (999);

BEGIN FOR r IN (

    SELECT

        table_name,

        owner

    FROM

        all_tables

    WHERE

        owner = l_owner_name

) LOOP 

l_table_name:=concat(concat(r.owner,'.'),r.table_name);

 l_sql_qry:= 'SELECT /*+ parallel */ count (*) FROM '||l_table_name;

    EXECUTE IMMEDIATE l_sql_qry INTO v_count;    

dbms_output.put_line(l_table_name ||' ' ||  v_count);

END LOOP; 

COMMIT;

END;

/

Option 2:

BEGIN

    dbms_scheduler.create_job('XXXXX_JOB_COUNT_TABLES_2', job_type => 'PLSQL_BLOCK', job_action => 'DECLARE     v_count        INTEGER;     l_owner_name   VARCHAR2(30) := ''PRODADMIN'';     l_table_name   varchar2(100);    l_sql_qry     VARCHAR2 (999);

BEGIN FOR r IN (    SELECT        table_name,        owner    FROM        all_tables    WHERE        owner = l_owner_name ) LOOP l_table_name:=concat(concat(r.owner,''.''),r.table_name);  l_sql_qry:= ''SELECT /*+ parallel */ count (*) FROM ''||l_table_name;   EXECUTE IMMEDIATE l_sql_qry INTO v_count;    INSERT INTO STATS_TABLE(TABLE_NAME,SCHEMA_NAME,RECORD_COUNT,CREATED) VALUES (r.table_name,r.owner,v_count,SYSDATE);END LOOP;  COMMIT; END;'

    , number_of_arguments => 0, start_date => systimestamp,

                              repeat_interval => NULL, end_date => NULL, job_class => 'DEFAULT_JOB_CLASS', enabled => TRUE, auto_drop

                              => false,

                              comments => 'A Job that count table rows');

END;

/