REM This script will display a report of the record count in the table and the estimated / calculated size of the table

set head off
set pagesize 0
set linesize 200
set feedback off
set termout off
drop table tempsize ;
drop table temprecs ;
create table tempsize (table_name varchar2(30), Max_Rec_Len number) ;
create table temprecs (table_name varchar2(30), Tot_Records number) ;
spool tmp_count.sql
select 'set head off' from dual;
select 'set pagesize 0' from dual;
select 'set linesize 80' from dual;
select 'set feedback off' from dual;
select 'insert into temprecs (select '||table_name|| ', count(*) from '||tabl
e_name||');' from user_tables
/
spool off
set termout on
start tmp_count;
insert into tempsize (select table_name ,
sum( decode( substr(data_type,1,1),
'N', trunc( ( data_precision + 1 ) / 2, 0 ) + 1 ,
'D', 7, data_length ) )
from user_tab_columns
group by table_name
having sum( decode( substr(data_type,1,1),
'N', trunc( ( data_precision + 1 ) / 2, 0 ) + 1 ,
'D', 7, data_length ) ) > 0 ) ;

select 'List of Tables with their Record Count and Sizes as of '||to_char(sysdat
e,'DD-MON-YY HH24:MI:SS') from dual ;

set pagesize 14 ;
set heading on ;
select a.table_name, b.tot_records, to_char(Max_Rec_Len*Tot_Records, '999,999,99
9,999') "Size(Bytes)", to_char(((Max_Rec_Len*Tot_Records)/1024)/1024, 99990.99)
"Size(Mb)" from tempsize a, temprecs b
where a.table_name=b.table_name and a.table_name not in ('TEMPSIZE','TEMPRECS')
order by (Max_Rec_Len*Tot_Records) desc ;
drop table tempsize ;
drop table temprecs ;
set feedback on ;