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 http://www.dba-village.com  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
as 
 t_cnt number default 0;
begin
 execute immediate 'select count(*) from '||i_table_name into t_cnt;
 return t_cnt;
end;
/
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 

'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

@"c:\checkempty_temp.sql"

Enjoy   :-)