Get Oracle table size

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.

 

In  Oracle 10g we have the dba_hist_seg_stat table with a wealth of information about all active segments within the database, including the space usage in the space_allocated_total and space_used_total columns.

This script will show "spaced used total" for a specific Oracle table, essentiall computing the Oracle table size over time:

 

col c1 format a15 heading 'snapshot|date'

col c2 format a25 heading 'table|name'

col c3 format 999,999,999 heading 'space|used|total'
 

select

   to_char(begin_interval_time,'yy/mm/dd hh24:mm')     c1,

   object_name      c2,

   space_used_total c3

from

   dba_hist_seg_stat       s,

   dba_hist_seg_stat_obj   o,

   dba_hist_snapshot       sn

where

   o.owner = 'SCHEMA_07'

and

   s.obj# = o.obj#

and

   sn.snap_id = s.snap_id

and

   object_name like 'XIF2%'

order by

   begin_interval_time;

 

As I note in my book Oracle Tuning: The Definitive Reference, we can quickly plot this information for trending and predictions:

snapshot        table 
date            name                      SPACE_USED_TOTAL 
--------------- ------------------------- ---------- 
06/03/24 07:03  XIF2IMM_FLIGHT_OCCURRENCE    24,340 
06/03/26 07:03  XIF2IMM_FLIGHT_OCCURRENCE    34,468