RE: How to identify objects that will fail to extend?

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
>

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