Rich, How about using the next_extent*2 compared with the select max(bytes) from dba_free_space where tablespace_name ='tablespace name in question'; That will show you if there is enough free space for two extents or more. I display sum(bytes) and max(bytes) to give a complete picture of the free space and then manually increasing the datafile(if possible) when there are multiple datafiles to allow for the needed expantion. Ron
>>> [EMAIL PROTECTED] 11/15/02 06:04PM >>> Okay. Let me try this! The largest column will have the biggest extent size that the tablespace can accommodate next time. You might save this information in a temp. table and have the other query to check against this. select substr(a.tablespace_name,1,20) tablespace, round(sum(a.total1)/1024/1024, 1) Total, round(sum(a.total1)/1024/1024, 1)-round(sum(a.sum1)/1024/1024, 1) used, round(sum(a.sum1)/1024/1024, 1) free, round(sum(a.sum1)/1024/1024, 1)*100/round(sum(a.total1)/1024/1024, 1) pct_free, round(sum(a.maxb)/1024/1024, 1) largest, max(a.cnt) fragments from (select tablespace_name, 0 total1, sum(bytes) sum1, max(bytes) MAXB, count(bytes) cnt from dba_free_space group by tablespace_name union select tablespace_name, sum(bytes) total1, 0, 0, 0 from dba_data_files group by tablespace_name) a group by a.tablespace_name -----Original Message----- Sent: Friday, November 15, 2002 4:54 PM To: Multiple recipients of list ORACLE-L Nope. If a segment has a NEXT EXTENT of 20M and the two largest contiguous free spaces in it's TS are 30M and 15M, the second extent (i.e. two extends to that segment) would fail, but would not show up in the query. That's what spawned the complexity of my SQL. Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: Friday, November 15, 2002 1:44 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: How to identify objects that will fail to extend? > > > If PCT_INCREASE is set to 0, then can't we simply compare > next_extent*2 > ( sub-query )? > > > -----Original Message----- > Sent: Friday, November 15, 2002 12:40 PM > To: Multiple recipients of list ORACLE-L > > > 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:[EMAIL PROTECTED]] > > 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] 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers 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).
