table_size.sql

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 '||table_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(sysdate,'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,999,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 ;