SQL to display change size history of a segment:
column owner format a16
column object_name format a36
column start_day format a11
column block_increase format 9999999999
select obj.owner, obj.object_name,
to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD hh24') start_day,
sum(a.db_block_changes_delta) block_changes,
sum(a.SPACE_USED_DELTA) SPACE_USED_DELTA,
sum(a.SPACE_ALLOCATED_DELTA) SPACE_ALLOCATED
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 ='&owner'
and obj.object_name ='&seg_name'
and end_interval_time between to_timestamp('01-JAN-1999','DD-MON-RRRR')
and to_timestamp('01-JAN-2999','DD-MON-RRRR')
group by obj.owner, obj.object_name,
to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD hh24')
order by start_day desc
;
---------------------------------------------------------------
|START_DAY |BLOCK_CHANGES|SPACE_USED_DELTA|SPACE_ALLOCATED|
---------------------------------------------------------------
|2014-MAR-05 04| 0 | 0 | 0 |
|2014-MAR-05 03| 87,520 | 5,141,804-| 0 |
|2014-MAR-05 02| 351,184 | 18,512,873-| 0 |
|2014-MAR-05 01| 545,920 | 49,948,868 | 67,108,864 |
|2014-MAR-05 00| 606,368 | 68,297,877 | 0 |
|2014-MAR-04 22| 616,480 | 19,457,197-| 0 |
|2014-MAR-03 22| 96,354,576 | 956,926,956 | 1,080,033,280 |
|2014-MAR-03 21| 163,731,904 | 1,735,144,707 | 1,990,197,248 |
|2014-MAR-03 20| 209,874,400 | 2,363,545,288 | 2,668,625,920 |
|2014-MAR-03 19| 68,288,784 | 791,862,298 | 978,321,408 |
|2014-MAR-03 16| 3,536 | 176,949 | 0 |
|2014-MAR-03 15| 6,784 | 257,722 | 0 |
|2014-MAR-03 14| 9,744 | 211,441-| 0 |
|2014-MAR-03 12| 4,320 | 279,758 | 0 |
|2014-MAR-03 00| 76,592 | 2,376,594 | 0 |
|2014-MAR-02 23| 176,256 | 5,067,188 | 0 |
|2014-MAR-02 22| 249,056 | 8,890,419 | 0 |
|2014-MAR-02 21| 172,784 | 5,782,929 | 0 |
|2014-MAR-02 20| 150,832 | 5,998,138 | 0 |