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
> to low of space in tablespace .
>
> -ak
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Joan Hsieh
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
