Thanks, but the next extent is the easy one. As I mentioned, I'm already running a similar query hourly.
Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:Govind.Arumugam@;alltel.com] > Sent: Friday, November 15, 2002 10:14 AM > To: Multiple recipients of list ORACLE-L > Subject: How to identify objects that will fail to extend? > > > 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 > [EMAIL PROTECTED] 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: [EMAIL PROTECTED] > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
