Re: script

From: Joan Hsieh <joan.hsieh_at_tufts.edu>
Date: Wed, 23 Jul 2003 11:36:00 -0400
Message-Id: <25998.339157@fatcity.com>

AK, this is a very old script.

set pagesize 999

spool extent.lst
ttitle 'Database Objects that will have Trouble Throwing Extents'

column owner format a10;
column segment_name format a22;
column segment_type format a10;
column tablespace_name format a14;
column next_extent format 999,999,999;

SELECT seg.owner, seg.segment_name,

       seg.segment_type, seg.tablespace_name,
       t.next_extent
   FROM sys.dba_segments seg,
        sys.dba_tables   t

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

            (select tablespace_name
               from dba_free_space free
              where free.tablespace_name =  t.tablespace_name
                and bytes               >=  t.next_extent     ))

UNION
SELECT seg.owner, seg.segment_name,

       seg.segment_type, seg.tablespace_name,
       DECODE (seg.segment_type,
               'CLUSTER',  c.next_extent)
   FROM sys.dba_segments seg,
        sys.dba_clusters c
WHERE   (seg.segment_type = 'CLUSTER'
  AND    seg.segment_name = c.cluster_name
  AND    seg.owner        = c.owner
  AND    NOT EXISTS
            (select tablespace_name
               from dba_free_space free
              where free.tablespace_name =  c.tablespace_name
                and bytes               >=  c.next_extent     ))

UNION
SELECT seg.owner, seg.segment_name,

       seg.segment_type, seg.tablespace_name,
       DECODE (seg.segment_type,
               'INDEX',    i.next_extent )
   FROM sys.dba_segments seg,
        sys.dba_indexes  i

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

            (select tablespace_name
               from dba_free_space free
              where free.tablespace_name =  i.tablespace_name
                and bytes               >=  i.next_extent     ))

UNION
SELECT seg.owner, seg.segment_name,

       seg.segment_type, seg.tablespace_name,
       DECODE (seg.segment_type,
               'ROLLBACK', r.next_extent)
   FROM sys.dba_segments seg,
        sys.dba_rollback_segs r

where (seg.segment_type = 'ROLLBACK'
  AND seg.segment_name = r.segment_name   AND seg.owner = r.owner
  AND NOT EXISTS

            (select tablespace_name
               from dba_free_space free
              where free.tablespace_name =  r.tablespace_name
                and bytes               >=  r.next_extent     ))

/
ttitle 'Segments that Are Sitting on the Maximum Extents Allowable '

select e.owner, e.segment_name, e.segment_type, count(*), avg(max_extents)
 from dba_extents e , dba_segments s
where e.segment_name = s.segment_name
  and e.owner = s.owner
group by e.owner, e.segment_name, e.segment_type having count(*) = avg(max_extents)
/

spool off

> AK wrote:
>
> where can i find a script to find segments which can't extend due
Received on Wed Jul 23 2003 - 10:36:00 CDT