Determine the Actual Size of the table

HI Guys,

If you have tables with a lot of DML especially delete and update, you need to consider to reorge that table as for sure the performance will be degradated, below is the command to determine the actual table size, if the difference between fragmented and actual size more than 10 persen ( overhead, ex : header, table directory in the block) then better to rebuild tables and its index also


note : we have to gather the statistic first
exec dbms_stats.gather_table_stats('SYSTEM','T');


** ACTUAL DATA SIZE **
select round((num_rows * avg_row_len /(1024)),2)||'KB' "size"
from dba_tables
where table_name = 'FRAGMENTED_TABLE';


** TABLE SIZE WITH FRAGMENTATION **
select table_name,round((blocks*db_block_size),2)||' KB' "size"
from dba_tables
where table_name = 'FRAGMENTED_TABLE';



#####   merge it into one single query ######

select table_name, round((blocks*8),2) tblsz_frgmnted_KB, round((num_rows*avg_row_len/1024),2) actualsize_KB from dba_tables
where table_name='FRAGMENTED_TABLE';

Enjoy :)