Hi there,

I as wondering if anyone could help with this SQL script.

I have created a table (cprodpcgg) into which I insert data dictionary information - this is for statistical reporting.

The script to insert is
insert into cprodpcgg
(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
where Owner = UPPER('sysadm');

This works fine.

Next I try to query the new table with the following script

select TS,
Seg_Owner,
Seg_Name,
Seg_Type,
MAX_EXTENTS,
lastweek,
today,
today-lastweek change
from ( select
TS,
Seg_Owner,
Seg_Name,
Seg_Type,
max_extents,
MAX(DECODE( trunc(sysdate)-Check_Date,7,
extents, 8, extents, 9, extents, 10, extents, 11,
extents, 12, extents, 13, extents, NULL)) LastWeek,
MAX(DECODE( trunc(sysdate)-Check_Date,0 ,
extents, 1, extents, 2, extents, 3, extents, 4,
extents, 5, extents, 6, extents, NULL)) Today
from &table
where extents >= 50
group by TS, Seg_Owner, Seg_Name, Seg_Type, max_extents);

This lets me look at information for this week and last week and compare database growth.

The problem that I am having is that If any element of the record is different from the last week then it's showing up twice
e.g

SEG_NAME SEG_TYPE MAX_EXTENTS LASTWEEK
---------------------- -------- -------------- --------
PS_VOUCHER... TABLE 2,147,483,645 92
PS_VOUCHER_LINE TABLE 110
PS_VOUCHER_LINE TABLE 2,147,483,645 69

The record for PS_VOUCHER_LINE is showing up twice because the max_extents has changed from 110 to unlimited.

Any help with a way round this would help me greatly, I have been working on this for weeks and can't see a solution.

Regards

Alison