Re: failed to extent to the next

From: Tim Sawmiller <sawmillert_at_state.mi.us>
Date: Fri, 06 Apr 2001 06:10:00 -0700
Message-ID: <F001.002E3DBA.20010406051117@fatcity.com>

What's with the complicated "where exists" stuff? I like mine better:

set echo off
set verify off
set feedback off
set heading off
set pagesize 60
set pause off
column tablespace_name format a10
column tablespace_name heading Tspace
create table max_free as
select tablespace_name, max(bytes) max_bytes from dba_free_space
group by tablespace_name
/
ttitle off
spool ext_max.lst
select value || ':' from v$parameter where name = 'db_name' /
select ' ' from dual
/
set heading on
prompt Objects Having Next Extent Size > Largest Available Extent ttitle on
ttitle 'Objects Having Next Extent Size > Largest Available Extent' select a.table_name object, 'Table' Type, a.tablespace_name,

    a.next_extent, b.max_bytes
from dba_tables a, max_free b
where a.next_extent > b.max_bytes
and a.tablespace_name = b.tablespace_name union
select a.partition_name object, 'Table Part.' Type, a.tablespace_name,

    a.next_extent, b.max_bytes
from dba_tab_partitions a, max_free b
where a.next_extent > b.max_bytes
and a.tablespace_name = b.tablespace_name union
select a.index_name object, 'Index' Type, a.tablespace_name,

    a.next_extent, b.max_bytes
from dba_indexes a, max_free b
where a.next_extent > b.max_bytes
and a.tablespace_name = b.tablespace_name union
select a.partition_name object, 'Index Part.' Type, a.tablespace_name,

    a.next_extent, b.max_bytes
from dba_ind_partitions a, max_free b
where a.next_extent > b.max_bytes
and a.tablespace_name = b.tablespace_name order by 1
/
drop table max_free
/
spool off
set pause on
set pagesize 20
set echo on
ttitle off

>>> Joan.Hsieh_at_mirror-image.com 04/05/01 05:06PM >>> 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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tim Sawmiller
  INET: sawmillert_at_state.mi.us

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 Fri Apr 06 2001 - 08:10:00 CDT