Confusing Query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Confusing Query

  1. #1
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    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

  2. #2
    Join Date
    Nov 2000
    Posts
    344
    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.


  3. #3
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    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

  4. #4
    Join Date
    May 2001
    Posts
    2
    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.

  5. #5
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    Many thanks Maryt

    That worked a treat.

    Alison

  6. #6
    Join Date
    May 2001
    Location
    Dallas Texas
    Posts
    8
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width