Friday, October 28, 2011

Script to Query all table row counts

Once I have to calculate the no. of rows of  all tables in a schema. It seems quite tedious to count rows of all the tables one by one  as  :
SQL>select count(*) from table_name;

The second method is to export the schemas virtually by using the parameter estimate of Datapump. Using this method,we can check the logfile for tables row counts . Even this method is not so efficient because if the schemas size is large then we will take long time. We can export the schemas as
C:\> expdp system/xxxx@noida directory=dpump  schemas=hr logfile=hrlog11.log dumpfile=hr.dmp ESTIMATE=BLOCK

Another option to find table row counts is to use the pl/sql scripts .I found this script from  and is quite efficient and useful. Here is the below script to check the no. of rows in tables. 

create or replace function table_count (i_table_name varchar2)
return number
 t_cnt number default 0;
 execute immediate 'select count(*) from '||i_table_name into t_cnt;
 return t_cnt;
show errors
select table_name, table_count(table_name) from user_tables;
drop function table_count;
Hence, this script allows for counting rows in tables without the need for sqlplus or temporary script files.

If we want to check the empty tables i.e; table not having in any rows can be found from the below scripts :

set termout off
col sql for a120
spool 'C:\checkempty_temp.sql'
set pages 0 feed off echo off
'select '''||owner ||''' owner,'''||table_name||''' table_name  '||chr(10)||
'FROM '||owner||'.'||table_name||' where rownum<2 having count(*)=0; ' sql
from all_tables
where owner not in ('SYS','SYSTEM','OUTLN','WMSYS') and substr(owner,1,4)<>'OPS$'
--and (blocks>0 or last_analyzed is null)
--and owner='XXXX'
--and blocks=0
order by 1;
spool off
set termout on pages 100

Enjoy   :-)

1 comment:

Anonymous said...

select table_name,num_rows from all_tables where owner='XXX' order by table_name;
will work..