Detecting Next Extent failure in Locally Managed Tablespace

[sjwales]

I've been looking around for a good script to monitor next extent failure conditions (i.e. the next extent an object is going to want is bigger than the biggest segment of freespace in that object's tablespace) for locally managed tablespaces.

We had a monitor in place when we were dictionary managed under Oracle 8, but since moving to Oracle 9, we've yet to be able to come up with a decent monitoring script.

I've looked at this one:

http://www.dbasupport.com/oracle/scripts/Detailed/217.shtml

But after running for 90 minutes against our largest production database it fails. Running it against a smaller non production database with a known next extent issue in it, it does report correctly, so I know it works, but we just can't make it work against the database we want to run it against.

For those of you using LMT and monitoring for this condition, do you have a script that you use that you'd be willing to share, or anyone have a reference URL? (my searching so far has found nothing).

Thanks
Steve

stephen.wales@riotinto.com

 

[SantaMufasa]

SJ,

An additional complicating factor with predictability is if your tablespace data files are in AUTOEXTEND mode...It's of little value to predictively monitor NEXT-EXTENT allocation if your data file still has plenty of room to EXTEND.

What are the values you are using in your failing predictive LMT script for "sizes and numbers" of extents?
(Actually, posting the contents of your script would probably be useful for us to help you.)

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:36 (09Jun04) UTC (aka "GMT" and "Zulu"), 14:36 (09Jun04) Mountain Time)

 

[sjwales]

Not using AUTOEXTEND, so that's not an issue.

The script I was playing with is in the link in the original article but reposted here:

REM Check Next Extents which could not be allocated.
REM Author Sanjay Gupta
REM This script also takes care of Locally Managed Tablespaces as well as Dictionary Managed Tablespace.
REM If you are using Locally Managed Tablespace(LMT) with the Autoallocate Segment Allocation, then there is no
REM Oracle provided algorithm to find the the value of next extent size. So, in case of LMT and Autoallocate
REM segment allocation ,this scripts assumes that the size of next extent would be same as size of previous
REM extent size atleast something is better than nothing.


SET LINESIZE 200
select b.tablespace_name "Tablespace",
b.segment_type "Type",
substr(ext.owner||'.'||ext.segment_name,1,50) "Object Name",
To_Char(decode(freespace.Extent_Management,'DICTIONARY',decode(b.extents,1,b.next_extent, ext.bytes * (1+b.pct_increase/100))
,'LOCAL',decode(freespace.Allocation_Type,'UNIFORM',freespace.INITIAL_EXTENT,'SYSTEM',ext.bytes)
)/1024,'9,999,999,999') "Required Extent(K)",
to_char(freespace.largest/1024,'9,999,999,999') "MaxAvail K"
from dba_segments b, dba_extents ext,
(select B.tablespace_name, B.Extent_Management, B.Allocation_Type, B.INITIAL_EXTENT, B.NEXT_EXTENT, max(A.bytes) largest
from dba_free_space A, dba_tablespaces B
Where B.Tablespace_Name = A.Tablespace_Name
And B.Status='ONLINE'
group by B.tablespace_name, B.Extent_Management, B.Allocation_Type, B.INITIAL_EXTENT, B.NEXT_EXTENT
) freespace
where
b.owner=ext.owner and
b.segment_type=ext.segment_type and
b.segment_name=ext.segment_name and
b.tablespace_name= ext.tablespace_name and
(b.extents-1) =ext.extent_id and
b.tablespace_name = freespace.tablespace_name and
decode(freespace.Extent_Management,'DICTIONARY',decode(b.extents,1,(b.next_extent),ext.bytes*(1+b.pct_increase/100))
,'LOCAL',decode(freespace.Allocation_Type,'UNIFORM',freespace.INITIAL_EXTENT,'SYSTEM',ext.bytes)
) > freespace.largest
order by b.Tablespace_Name,b.Segment_Type,b.Segment_Name
/

The previous one we used under dictionary managed tablespaces was a fairly simple one - comparing next_extent from dba_tables against the largest segment of freespace - but that's irrelevant for this discussion since tables under LMT don't have a next_extent value.

stephen.wales@riotinto.com