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
where Owner = UPPER('sysadm');
This works fine.
Next I try to query the new table with the following script
from ( select
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
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
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.
Click Here to Expand Forum to Full Width