Script: Object or Table Growth from AWR

Hello All,

Sometimes we were been asked to provide the object or a table growth, Here is some nice script, which is useful to find the table growth per day for a given table;

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        obj.object_name='TEST_HIST'
  and      end_interval_time between to_timestamp('01-JAN-2012','DD-MON-RRRR')
           and to_timestamp('29-NOV-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
  /


OWNER            OBJECT_NAME        START_DAY   BLOCK_INCREASE
---------------- -------------  -----------     --------------
TEST             TEST_HIST      2012-NOV-22          18704
TEST             TEST_HIST      2012-NOV-23           9968
TEST             TEST_HIST      2012-NOV-26          10688
TEST             TEST_HIST      2012-NOV-27          10064
TEST             TEST_HIST      2012-NOV-28          10336
SQL> SQL>

The output shows the number of blocks that increased per day, you can calculate blocks & db_block_size and get the exact size if required.

Update: Another method as updated latest by Laurent in his blog post at http://laurentschneider.com/wordpress/2012/12/how-big-was-my-table-yesterday.html

SELECT savtime,owner,object_name,rowcnt,blkcnt
FROM sys.WRI$_OPTSTAT_TAB_HISTORY w,
  dba_objects o
WHERE 
   o.owner='SCOTT'
   AND o.object_name='EMP'
   and o.object_id = W.OBJ#
ORDER BY o.owner, o.object_name, w.savtime;

 
SAVTIME           OWNER    OBJECT_NAME     ROWCNT     BLKCNT
----------------- -------- ----------- ---------- ----------
2012-11-06 06:49  SCOTT    EMP           13215425     120077
2012-11-13 07:28  SCOTT    EMP           12678535     120077
2012-11-20 03:15  SCOTT    EMP           12860640     120077
2012-11-27 03:19  SCOTT    EMP           13045850     120077
2012-12-04 05:41  SCOTT    EMP           13326460     120077

You can find other useful growth trend scripts here in this post:- http://db.geeksinsight.com/2012/10/15/scripts-databasetabletablespace-growth-report-using-awr/

-Hope this helps
Thanks
Geek DBA