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

>>> Govind.Arumugam_at_alltel.com 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
Rich.Jesse_at_qtiworld.com              Quad/Tech International, Sussex,

> 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
> Rich.Jesse_at_qtiworld.com Quad/Tech International,
> Sussex, WI USA
>
> > -----Original Message-----
> > From: Govind.Arumugam_at_alltel.com

[mailto:Govind.Arumugam_at_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
> > 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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ron Rogers
  INET: RROGERS_at_galottery.org

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