Date Adaptive Script
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Date Adaptive Script

Hybrid View

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

    Wink

    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..

    select
    TS,
    Seg_Owner,
    Seg_Name,
    Seg_Type,
    MAX_EXTENTS,
    MAX(DECODE(Check_Date, TRUNC(SysDate),
    Blocks,0)) Blocks,
    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),
    extents,0)) -
    MAX(DECODE(Check_Date, TRUNC(SysDate-7),
    extents,0)) Change
    from APRODBSEC
    WHERE EXTENTS >= 50
    group by
    TS,
    Seg_Owner,
    Seg_Name,
    Seg_Type,
    MAX_EXTENTS
    order by
    TS
    ;

    This script worked great until it was ran against the table on a different day and it was inserted,

    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 there is no output.

    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

    Any help would be a life saver.

    Allie

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Where did you find APRODBSEC table?

  3. #3
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    APRODBSEC is a table I created to hold space information extracted from the data dictionary, I use the following script to insert this information

    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)

    Cheers.

    A

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