How to identify objects that will fail to extend?

From: <Govind.Arumugam_at_alltel.com>
Date: Fri, 15 Nov 2002 08:13:44 -0800
Message-ID: <F001.005044C7.20021115081344@fatcity.com>

List,

There was a question as to how to identify objects that will fail to extend?

This is what we do.

SELECT owner, tablespace_name, segment_name, next_extent FROM dba_segments ds
WHERE tablespace_name != 'TEMP'
  AND next_extent > ( SELECT max(bytes)

                      FROM dba_free_space
                      WHERE tablespace_name=ds.tablespace_name)

ORDER BY 1, 2; -----Original Message-----
Sent: Thursday, November 14, 2002 4:54 PM To: Multiple recipients of list ORACLE-L

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Govind.Arumugam_at_alltel.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 Fri Nov 15 2002 - 10:13:44 CST