Query to predict failure on second extent of segment

From: Jesse, Rich <Rich.Jesse_at_qtiworld.com>
Date: Thu, 14 Nov 2002 13:53:57 -0800
Message-ID: <F001.00503C7A.20021114135357@fatcity.com>

Hi all,

Until a whole mass of astrological confluences happen, I'm stuck with dictionary-managed tablespaces on 8.1.7 on HP/UX 11.0. And we're having some space/growth issues right now that I want (need!) to be more proactive with. So, based on several factors -- most political -- I want to run a daily report that tells me when a segment will not be able to extend twice.
(We're already running the single extent failure hourly.)

After looking on the net, I found some queries to do this, but all I saw were severely flawed. So, I rolled my own. The only problem I can see with it for dictionary TSs is when the RANK() has multiple matches for first and second (e.g. TS "MY_BIG_TS" has it's largest contiguous free spaces of 40M, 10M, and 10M). Unfortunately, I'm stumped as to how to prevent this.

Anyone care to comment on this load of SQueaL? Thx! :)

Rich

Rich Jesse                           System/Database Administrator
Rich.Jesse_at_qtiworld.com              Quad/Tech International, Sussex, WI USA



SELECT ds.owner, ds.segment_name, ds.segment_type, ds.tablespace_name,
	ds.next_extent/1024 "Next ext", fs2.max_free/1024 "Max Free",
	fs2.min_free/1024 "2nd Max Free", fs2.free_spaces

FROM dba_segments ds,
(

	SELECT tablespace_name, MAX(bytes) max_free, MIN(bytes) min_free,
		count(*) free_spaces
	FROM
	(
		SELECT tablespace_name, bytes,
			RANK() OVER (PARTITION BY tablespace_name
				ORDER BY tablespace_name, bytes DESC)
byte_rank
		FROM dba_free_space
	)
	WHERE byte_rank < 3
	GROUP BY tablespace_name

) fs2
WHERE ds.segment_type IN ('INDEX','TABLE') AND fs2.tablespace_name = ds.tablespace_name AND (

	((ds.next_extent > fs2.min_free OR fs2.free_spaces < 2)
		AND ds.next_extent*2 > fs2.max_free)
	OR ds.next_extent > fs2.max_free
	)

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Received on Thu Nov 14 2002 - 15:53:57 CST