-
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
-
Where did you find APRODBSEC table?
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|