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: [EMAIL PROTECTED]
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: [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).
