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
>>> [EMAIL PROTECTED] 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: [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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tim Sawmiller
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).
