Get Oracle table size Oracle Database Tips by Donald Burleson

Question:  What script do I run to get the size of a table?

Answer:  First, we must remember that Oracle table size is constantly changing as DML adds and re moves rows.  Also, we have many other factors that determine the "actual" size of a table:

• The high water mark for the Oracle table

• The number of allocated extents

• The amount of spaced reserved on each block for row expansion (pctfree)

You need to ask yourself exactly which Oracle table size, you wish to query:

• Do you want only the row space consumed? ( select avg_row_len*num_rows from dba_tables)
   

• Do you want to include allocated file space for the table? (select . . . from dba_segments)
   

• Do you want to include un-used extent space? (select . . . from dba_data_files, dba_extents . . )
   

• Do you want to include un-used space up to the high water mark?  This may over-estimate the real Oracle table size.
   

• Do you want table sizes for Oracle growth monitoring?

There are many Oracle scripts for computing the size of a table, depending on these factors, but the best is the Oracle script collection which has many scripts to compute table sizes, depending on your needs.

select
   segment_name           table_name,   
   sum(bytes)/(1024*1024) table_size_meg
from  
   user_extents
where 
   segment_type='TABLE'
and   
   segment_name = 'MYTAB'
group 
See code depot for full scripts.