Historical Growth of Objects

Hi there,

I am running the following script to give me information about the size and growth of objects..

All data is pulled from the data_dictionary except check_date which is sysdate..

insert into APRODBSEC
( TS , Seg_Owner , Seg_Name , Seg_Type , bytes , Blocks , Extents
next_extent , max_extents , Check_Date)
select
d.Tablespace_Name, d.Owner, d.Segment_Name, d.Segment_Type,
d.bytes, d.blocks, d.Extents, d.next_extent, d.max_extents, TRUNC(SysDate)
from DBA_SEGMENTS@APRODBSEC.world D
where Owner = UPPER('schema_owner')

DBA_SEGMENTS@APRODBSEC.WORLD is a database link I have set up to another database on another server (this all works fine)

I am looking to pull information from this table which will give me information of the growth of the objects.

I have a script which works using a decode function
select
TS,
Seg_Owner,
Seg_Name,
Seg_Type,
MAX_EXTENTS,
MAX(DECODE(Check_Date, TRUNC(SysDate-7),
extents,0)) LastWeek,
MAX(DECODE(Check_Date, TRUNC(SysDate),
extents,0)) Today,
MAX(DECODE(Check_Date, TRUNC(SysDate-7),
extents,0)) Change
from APRODBSEC
group by
TS,
Seg_Owner,
Seg_Name,
Seg_Type,
MAX_EXTENTS;

The problem with this is that if the script is ran on a different day than the data is inserted I get no output

This means for instance if the data was inserted into the table on 27-FEB-01 and the script was run on 01-MAR-01 the DECODE part show up with a 0.

I am looking for help in adapting this scrip so that it works no matter what day it's ran..

I was thinking of something using
CHECK_DATE-(MAX(CHECK_DATE)-TRUNC(SYSDATE))
but I can't get it to work

Many thanks to anyone who can help

Alison