OK, once again, this isn't what I'm looking for. Comparing NEXT_EXTENT*2 to the largest free space will only work in some cases. Consider that a segment has NEXT_EXTENT of 30M and there are two 40M free spaces in the TS. The segment clearly has enough room to extend twice, but comparing NEXT_EXTENT*2 to the largest free space may not see this.
I've changed the subject back to reflect the original thread that I started. Thanks, Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA > -----Original Message----- > From: Ron Rogers [mailto:[EMAIL PROTECTED]] > Sent: Monday, November 18, 2002 6:49 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: How to identify objects that will fail to extend? > > > 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).
