-
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
-
It looks like your cprodpcgg table has 2 rows in it for that one table.
If you change your insert query to this :
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')
and (owner, segment_name) not in
(select seg_owner, seg_name
from cprodpcgg);
Then it will not insert more than one row per segment.
-
Hi Jdorlon, many thanks for your reply.
I don't think this will work though.
I run the insert weekly, to see the growth of the databases,
If I'm reading your script correctly, If the segment already exists in the stats table (which it will) the row won't insert.
This will then mean that I wont see week to week growth.
If I'm not understanding correctly please let me know.
Regards
Alison
-
If you don't want a difference in max_extents from one week to the next to result in multiple entries for the segment, then you can't include max_extents in your group by clause. My suggestion: take it out of the group by and maybe ask for max(max_extents) in your query.
-
Many thanks Maryt
That worked a treat.
Alison
-
I have a PL/SQL package that might do what you want as far as tracking object growth. I wrote it for PeopleSoft way back in version 5 on Oracle 7.2, and have kept it mostly maintained for everything up to PeopleSoft 7.5 on Oracle 7.3.4. E-mail me if you are interested. robert.fendley@ps.net
