Check Oracle Database table growth

In DBA job, sometime we need to check table growth rate. For check table growth we need to enable snapshot of database. This snapshot will keep information of database in capture time. Base on this snapshot, we can calculate table growth rate. 

To view the object (segment) growth in blocks, you can use the views dba_hist_seg_stat and dba_hist_snapshot like below.

select   obj.owner, obj.object_name, 
         to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD') start_day,
         sum(a.db_block_changes_delta) block_increase
from     dba_hist_seg_stat a,
         dba_hist_snapshot sn,
         dba_objects obj
where    sn.snap_id = a.snap_id
and      obj.object_id = a.obj#
and      obj.owner not in ('SYS','SYSTEM')
and      end_interval_time between to_timestamp('01-JAN-2012','DD-MON-RRRR') 
         and to_timestamp('02-FEB-2012','DD-MON-RRRR')
group by obj.owner, obj.object_name, 
         to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD')
order by obj.owner, obj.object_name

This script check only increase database block, if you want change from block to OS size we must know database block size. In default , database block size is 8192 Bytes.


When you summary total increase size  of table and compare with increase size of database, you will see it not equal. Because, this command don't minus delete block of table, and calculate database flashback retention, recycle bin and some table is template table. Template table will automatic drop at end of session, then size of table will return to be zero.

Reference: Doc ID 1395195.1