failed to extent to the next

From: Joan Hsieh <Joan.Hsieh_at_mirror-image.com>
Date: Thu, 05 Apr 2001 13:04:46 -0700
Message-ID: <F001.002E3263.20010405130645@fatcity.com>

Hi Listers,

I used to use this script detect the tablespace fail to extent to the next and sent email to me everyday. It works fine. However, the other dba think next extent is not good enough to get quick responds since we have so much volume transactions going on. He want to detect the tablespace can't extent to the next 12 extents. I tried couple ways, (bytes * 12) but that result not 100% correct. Thanks in advance if someone can share your ideas.

Joan

SELECT seg.owner


      ,seg.segment_name

      ,seg.segment_type  SEG_TYPE

      ,seg.tablespace_name

      ,t.next_extent

      ,max(f.bytes) largest_space

      ,count(f.bytes) num_free

FROM   sys.dba_segments seg


      ,sys.dba_tables   t

      ,sys.dba_free_space f

WHERE  seg.segment_type = 'TABLE'
AND    seg.segment_name = t.table_name
AND    seg.owner        = t.owner

AND t.tablespace_name = f.tablespace_name AND NOT EXISTS

           (select tablespace_name
            from   dba_free_space free
            where  free.tablespace_name = t.tablespace_name
            and    bytes               >= t.next_extent)
GROUP BY  seg.owner
         ,seg.segment_name
         ,seg.segment_type
         ,seg.tablespace_name
         ,t.next_extent

UNION

SELECT seg.owner


      ,seg.segment_name

      ,seg.segment_type SEG_TYPE

      ,seg.tablespace_name

      ,DECODE (seg.segment_type,'CLUSTER',c.next_extent)

      ,max(f.bytes) largest_space

      ,count(f.bytes) num_free

FROM   sys.dba_segments seg


      ,sys.dba_clusters c

      ,sys.dba_free_space f

WHERE  seg.segment_type = 'CLUSTER'
AND    seg.segment_name = c.cluster_name
AND    seg.owner        = c.owner

AND c.tablespace_name = f.tablespace_name AND NOT EXISTS

            (select tablespace_name
             from   dba_free_space free
             where  free.tablespace_name = c.tablespace_name
             and    bytes               >= c.next_extent)
GROUP BY  seg.owner
         ,seg.segment_name
         ,seg.segment_type
         ,seg.tablespace_name
         ,c.next_extent

UNION

SELECT seg.owner


      ,seg.segment_name

      ,seg.segment_type

      ,seg.tablespace_name

      ,DECODE (seg.segment_type,'INDEX',i.next_extent)

      ,max(f.bytes) largest_space

      ,count(f.bytes) num_free

FROM   sys.dba_segments seg


      ,sys.dba_indexes  i

      ,sys.dba_free_space f

WHERE  seg.segment_type = 'INDEX'
AND    seg.segment_name = i.index_name
AND    seg.owner        = i.owner

AND i.tablespace_name = f.tablespace_name AND NOT EXISTS

            (select tablespace_name
             from   dba_free_space free
             where  free.tablespace_name = i.tablespace_name
             and    bytes               >= i.next_extent)
GROUP BY  seg.owner
         ,seg.segment_name
         ,seg.segment_type
         ,seg.tablespace_name
         ,i.next_extent

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Joan Hsieh
  INET: Joan.Hsieh_at_mirror-image.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).

Received on Thu Apr 05 2001 - 15:04:46 CDT