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.
06-15-2001, 10:13 AM
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)
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)
where Owner = UPPER('sysadm')
and (owner, segment_name) not in
(select seg_owner, seg_name
Then it will not insert more than one row per segment.
06-18-2001, 04:49 AM
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.
06-18-2001, 09:12 AM
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.
06-19-2001, 07:16 AM
Many thanks Maryt
That worked a treat.
06-19-2001, 09:49 AM
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. firstname.lastname@example.org