Oracle Count the number of rows in tables in current SCHEMA

Oracle Row count in schema
Dynamic sql example
execute immediate sql example




set serveroutput on size 1000000

DECLARE
  t_c1_tname      user_tables.table_name%TYPE;
  t_command       varchar2(200);
  t_cid           integer;
  t_total_records number(10);
  stat            integer;
  row_count       integer;
  t_limit         integer := 0;    -- <<<<<<<<<<<<<<<<<<Only show tables with more rows
  cursor c1 is select table_name from user_tables order by table_name;
BEGIN
  t_limit := 0;
  open c1;
  loop
        fetch c1 into t_c1_tname;
        exit when c1%NOTFOUND;
        t_command := 'SELECT COUNT(0) FROM '||t_c1_tname;
        t_cid := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(t_cid,t_command,dbms_sql.native);
        DBMS_SQL.DEFINE_COLUMN(t_cid,1,t_total_records);
        stat := DBMS_SQL.EXECUTE(t_cid);
        row_count := DBMS_SQL.FETCH_ROWS(t_cid);
        DBMS_SQL.COLUMN_VALUE(t_cid,1,t_total_records);
        if t_total_records > t_limit then
     DBMS_OUTPUT.PUT_LINE(rpad(t_c1_tname,55,' ')|| to_char(t_total_records,'99999999')||' record(s)');
        end if;
        DBMS_SQL.CLOSE_CURSOR(t_cid);
  end loop;
  close c1;
END;
/



declare
 
  tbl1 varchar2(40);
  sql1 varchar2(2000);
  l_cnt    varchar2(30);

begin

for i in (select table_name from user_tables) loop

  tbl1 := i.table_name ;
  sql1 := 'select count(*) from ' || tbl1 ;
  execute immediate sql1 into l_cnt ;

dbms_output.put_line(tbl1||': '|| l_cnt);

end loop;
end;
/


BONUS: 0
SALGRADE: 5
MYEMP: 2
EMP: 14
ANUJ_BIG_TABLE: 9999
ANUJ_BIG_TABLE1: 9999
AA: 14
TEST: 0
ANUJ10: 0
MYEMP_WORK: 0






set serveroutput on size 1000000

DECLARE
t_c1_tname user_tables.table_name%TYPE;
t_command varchar2(200);
t_cid integer;
t_total_records number(10);
stat integer;
row_count integer;
t_limit integer := 0; -- Only show tables with 0 rows
cursor c1 is select table_name from user_tables where tablespace_name = 'USERS';
BEGIN
t_limit := 0;
open c1;
loop
fetch c1 into t_c1_tname;
exit when c1%NOTFOUND;
t_command := 'SELECT COUNT(0) FROM '||t_c1_tname;
t_cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(t_cid,t_command,dbms_sql.native);
DBMS_SQL.DEFINE_COLUMN(t_cid,1,t_total_records);
stat := DBMS_SQL.EXECUTE(t_cid);
row_count := DBMS_SQL.FETCH_ROWS(t_cid);
DBMS_SQL.COLUMN_VALUE(t_cid,1,t_total_records);
if t_total_records = t_limit then
DBMS_OUTPUT.PUT_LINE(rpad(t_c1_tname,55,' ')||to_char(t_total_records,'99999999')||' record(s)');

end if;
DBMS_SQL.CLOSE_CURSOR(t_cid);
end loop;
close c1;
END;
/



TEST                                                           0 record(s)
ANUJ10                                                         0 record(s)
BONUS                                                          0 record(s)




set serveroutput on size 1000000
declare
  t_c1_tname      dba_tables.table_name%type;
  t_c1_owner      dba_tables.owner%type;
  t_command       varchar2(200);
  t_cid           integer;
  t_total_records number(10);
  stat            integer;
  row_count       integer;
  t_limit         integer := 1;    -- <<<<<<<<<<<<<<<<<<Only show tables with more then 1 rows
begin
  for i in (select owner,table_name from dba_tables where owner='ANUJ') LOOP
  t_limit := 1;
        t_command := 'SELECT COUNT(0) FROM '||i.owner||'.'||i.TABLE_NAME;
        t_cid := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(t_cid,t_command,dbms_sql.native);
        DBMS_SQL.DEFINE_COLUMN(t_cid,1,t_total_records);
        stat := DBMS_SQL.EXECUTE(t_cid);
        row_count := DBMS_SQL.FETCH_ROWS(t_cid);
        DBMS_SQL.COLUMN_VALUE(t_cid,1,t_total_records);
        if t_total_records > t_limit then
     DBMS_OUTPUT.PUT_LINE(rpad(i.TABLE_NAME,55,' ')|| to_char(t_total_records,'99999999')||' record(s)');
        end if;
        DBMS_SQL.CLOSE_CURSOR(t_cid);
  end loop;

end;
/


TEST                                                       10000 record(s)
ANUJ_BIG_TABLE                                           5119488 record(s)
DEPT                                                           4 record(s)
T_CHILD                                                       12 record(s)
T_PARENT                                                      10 record(s)
TEST9                                                     400000 record(s)
EMP_IND                                                  3670016 record(s)
BV_DEMO                                                    19999 record(s)
TEST_TAB                                                20000000 record(s)
CUST                                                         900 record(s)
T1                                                         10000 record(s)





    set serveroutput on size 100000 feedback off
     declare
       rs     integer;     
       cur    integer;     
       rp     integer;     
       trs    integer;     
       n      integer;     
       un     varchar2(30);
     begin
        dbms_output.put_line(rpad('Table Name',40)||' Number of Rows      Created Date');
        dbms_output.put_line(rpad('-',73,'-'));
        cur:= dbms_sql.open_cursor;
          for  t  in  (select  object_name, created from user_objects where object_type='TABLE') loop
            dbms_sql.parse(cur,'select count(*) from ' || t.object_name, dbms_sql.v7);
            dbms_sql.define_column(cur, 1, rs);
            rp:= dbms_sql.execute(cur);
            n:=dbms_sql.fetch_rows(cur);
            dbms_sql.column_value(cur, 1, rs);
            dbms_output.put_line(rpad(t.object_name,48,'.')||rpad(rs,15,'.')||t.created);
        end loop;
        dbms_sql.close_cursor(cur);
        select  count(*)  into  n  from  tab where tabtype='TABLE';
        select  user  into  un  from  dual;
        dbms_output.put_line(rpad('-',73,'-'));
        dbms_output.put_line(un||' User contain '||n||' Table(s)');
     end;
     /
     set  serveroutput off  feedback  on  feedback 6




Table Name                               Number of Rows      Created Date
-------------------------------------------------------------------------
T2..............................................18.............17-NOV-11
T...............................................7..............18-NOV-11
SALGRADE........................................5..............22-SEP-11
ORDER_LINES.....................................0..............11-NOV-11
ORDERS..........................................0..............11-NOV-11
MYEMP_WORK......................................0..............22-SEP-11
MYEMP...........................................2..............22-SEP-11
EMP.............................................14.............22-SEP-11
DEPT............................................4..............22-SEP-11
CHAINED_ROWS....................................0..............23-NOV-11
BONUS...........................................0..............22-SEP-11
ANUJ_BIG_TABLE1.................................9999...........01-NOV-11
ANUJ_BIG_TABLE..................................9999...........31-OCT-11
ANUJ10..........................................0..............28-SEP-11
AA..............................................14.............04-NOV-11
-------------------------------------------------------------------------
SCOTT User contain 15 Table(s)




col TXT for a60 heading "Table Row Count "
select rpad(table_name,30,' ')    || ' ===> '     || extractvalue  (xmltype  (dbms_xmlgen.getxml (   'select count(*) cnt from '   || owner ||'.'||table_name  )  )
         ,'//CNT'  ) txt from all_tables
where owner=upper('&Owner') ;


Table Row Count
------------------------------------------------------------
TEST11                         ===> 2
ANUJ                           ===> 4
EMP8                           ===> 3
DEPT                           ===> 4
EMP                            ===> 14
BONUS                          ===> 0
SALGRADE                       ===> 5

7 rows selected.

==========

define TABLE_OWNER='XXX'

define TABLE_NAME='AUDIT_LOG'

set serverout on size 1000000  verify off

declare

sql_stmt varchar2(1024);

row_count number;

cursor get_tab is

select table_name,partition_name from dba_tab_partitions where table_owner=upper('&TABLE_OWNER') and table_name='&TABLE_NAME'

--and rownum<20

;

begin

dbms_output.put_line('Record Counts for table_name.....  ');

dbms_output.put_line('Log file to numrows_part_&TABLE_OWNER.lst ....');

dbms_output.put_line('....');

for get_tab_rec in get_tab loop

BEGIN

sql_stmt := 'select count(*) from &TABLE_OWNER..'||get_tab_rec.table_name||' partition ( '||get_tab_rec.partition_name||' )';

EXECUTE IMMEDIATE sql_stmt INTO row_count;

dbms_output.put_line('Table '||rpad(get_tab_rec.table_name||'('||get_tab_rec.partition_name||')',50)||' '||TO_CHAR(row_count)||' rows.');

exception when others then dbms_output.put_line ('Error counting rows for table '||get_tab_rec.table_name);

END;

end loop;

end;

/

set verify on

====

define table_owner='SCOTT'

set serverout on size 1000000

set verify off

declare

sql_stmt varchar2(1024);

row_count number;

v_table_name varchar2(40);

cursor get_tab is

select table_name

from dba_tables

where owner=upper('&&TABLE_OWNER');

begin

dbms_output.put_line('Checking Record Counts for table_name');

dbms_output.put_line('Log file to numrows_part_&&TABLE_OWNER..lst ....');

dbms_output.put_line('....');

for get_tab_rec in get_tab 

loop

v_table_name := get_tab_rec.table_name;

sql_stmt := 'select count(*) from &&TABLE_OWNER..'||get_tab_rec.table_name;

dbms_output.put_line(sql_stmt);

EXECUTE IMMEDIATE sql_stmt INTO row_count;

dbms_output.put_line('Table '||rpad(get_tab_rec.table_name,50)||' '||TO_CHAR(row_count)||' rows.');

END loop;

EXCEPTION 

when others then

dbms_output.put_line('Error counting rows for table '||v_table_name);

end;

/

define table_owner='SCOTT'

define TABLE_NAME='SALES_PART'

set serverout on size 1000000

set verify off

declare

sql_stmt varchar2(1024);

row_count number;

cursor get_tab is

select table_name,partition_name

from dba_tab_partitions

where table_owner=upper('&&TABLE_OWNER') 

and table_name='&&TABLE_NAME'

;

begin

dbms_output.put_line('Checking Record Counts for table_name');

dbms_output.put_line('Log file to numrows_part_&&TABLE_OWNER.lst ....');

dbms_output.put_line('....');

for get_tab_rec in get_tab loop

BEGIN

sql_stmt := 'select count(*) from &&TABLE_OWNER..'||get_tab_rec.table_name||' partition ( '||get_tab_rec.partition_name||' )';

EXECUTE IMMEDIATE sql_stmt INTO row_count;

dbms_output.put_line('Table '||rpad(get_tab_rec.table_name||'('||get_tab_rec.partition_name||')',50)||' '||TO_CHAR(row_count)||' rows.');

exception when others then

dbms_output.put_line

('Error counting rows for table '||get_tab_rec.table_name);

END;

end loop;

end;

/

set verify on

=====

 

define 1 ='ANUJ';

 

set serveroutput on

declare 

p_count number;

begin

for i in (select * from DBA_tab_partitions where table_name='&1') loop 

 execute immediate 'select count(*) from '||i.TABLE_OWNER||'.'||i.TABLE_NAME|| ' partition('||i.partition_name||')' into p_count;

 dbms_output.put_line(i.partition_name||' '||to_char(p_count));

 end loop;

 end ;

 

 

===

with creation date !!!

define 1 ='XXXXXX';

set serveroutput on

declare

p_count number;

begin

for i in (select owner,object_name,subobject_name,created from dba_objects  where object_name='&1' and owner= 'RRRR'   and object_type='TABLE PARTITION' order by created) loop

execute immediate 'select /*+ parallel(112) */ count(*) from '||i.OWNER||'.'||i.object_name||' partition('||i.subobject_name||')' into p_count;

dbms_output.put_line(to_char(i.created,'dd-mm-yyyy hh24:mi:ss')||'  ' ||i.subobject_name||' '||to_char(p_count));

end loop;

end ;

===

define 1 ='XXXX';

set serveroutput on

declare

p_count number;

begin

for i in (select owner,object_name,subobject_name,created from dba_objects  where object_name='&1' 

and owner= 'XXX'   

and object_type='TABLE PARTITION' and created > TRUNC(SYSDATE+1) - INTERVAL '1' YEAR 

order by created) loop

execute immediate 'select /*+ parallel(112) */ count(*) from '||i.OWNER||'.'||i.object_name||' partition('||i.subobject_name||')' into p_count;

dbms_output.put_line(to_char(i.created,'dd-mm-yyyy hh24:mi:ss')||'  ' ||i.subobject_name||' '||to_char(p_count));

end loop;

end ;

set pagesize 0

select 'prompt PARTITION_NAME-- '||PARTITION_NAME , 'SELECT count(*),MIN("time") min , max("time")  MAX  from reports.reports  partition ('||PARTITION_NAME ||');'from dba_tab_partitions 

where 1=1 and TABLE_NAME='XXX'

set pagesize 0 

select 'prompt PARTITION_NAME-- '||PARTITION_NAME , 'SELECT count(*),to_char(MIN("time"),''dd-mm-yyyy hh24:mi:ss'') min ,

        to_char(max("time"),''dd-mm-yyyy hh24:mi:ss'')  MAX  from Anuj.test  partition ('||PARTITION_NAME ||');'

from dba_tab_partitions where 1=1 

and TABLE_NAME='XXX'

/