How to calculate table size

Sometime we need to estimate the size of the table for storage planing. But looking at the database, it doesn't shows us directly how many space is actually occupied by a particular table. A bit of peek on the USER_SEGMENTS and USER_TABLES views will do the trick.

To query the actual space allocated for the table:

select segment_name, bytes/1024/1024,blocks MB
from user_segments
where segment_name = 'T1'

or

select (BLOCKS*[replace your data block size here])
from user_tables
where table_name = 'T1';


To query the actual space occupied by the data:

select table_name,(num_rows*avg_row_len)/(1024*1024) MB
from user_tables
where table_name='T1';

or

select ((BLOCKS*[replace your data block size here])-(BLOCKS*AVG_SPACE))/1024/1024 MB
from user_tables
where table_name='T1'

Somehow the second method will show bigger amount of spaces being occupied.


Note: we need to analyzed the table before we can get all these statistic.