DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Historical Growth of Objects

  1. #1
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200

    Unhappy

    Hi there,

    I am running the following script to give me information about the size and growth of objects..

    All data is pulled from the data_dictionary except check_date which is sysdate..

    insert into APRODBSEC
    ( 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@APRODBSEC.world D
    where Owner = UPPER('schema_owner')

    DBA_SEGMENTS@APRODBSEC.WORLD is a database link I have set up to another database on another server (this all works fine)

    I am looking to pull information from this table which will give me information of the growth of the objects.

    I have a script which works using a decode function
    select
    TS,
    Seg_Owner,
    Seg_Name,
    Seg_Type,
    MAX_EXTENTS,
    MAX(DECODE(Check_Date, TRUNC(SysDate-7),
    extents,0)) LastWeek,
    MAX(DECODE(Check_Date, TRUNC(SysDate),
    extents,0)) Today,
    MAX(DECODE(Check_Date, TRUNC(SysDate-7),
    extents,0)) Change
    from APRODBSEC
    group by
    TS,
    Seg_Owner,
    Seg_Name,
    Seg_Type,
    MAX_EXTENTS;

    The problem with this is that if the script is ran on a different day than the data is inserted I get no output

    This means for instance if the data was inserted into the table on 27-FEB-01 and the script was run on 01-MAR-01 the DECODE part show up with a 0.

    I am looking for help in adapting this scrip so that it works no matter what day it's ran..

    I was thinking of something using
    CHECK_DATE-(MAX(CHECK_DATE)-TRUNC(SYSDATE))
    but I can't get it to work


    Many thanks to anyone who can help

    Alison

  2. #2
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200

    Wink

    Sorry to reply to my own message but could someone please see if they have any advice for me, I'm getting desperate.

    Many thanks

    Allie

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