Joan,
I think if you replaced the
and 12 > (select count(*)
with
and 12 > (select sum(floor(fs2.bytes/s.next_extent)) it would work as
this should give the count * the number of times the next extent could fit
into the large extents.
Doesn't deal with pctincrease other than 0 though.
Cheers
Iain Nicoll
-----Original Message-----
<mailto:[EMAIL PROTECTED]> ]
Sent: 06 April 2001 16:15
To: Multiple recipients of list ORACLE-L
Jacques,
I got one it works fine. However, the report is not 100% correct. For
instance, my next extent is 250m, if I select * from dba_free_space where
tablespace_name='XPC_OBJ_LOB' , it still report to me not enough space for
next 12 extent, But it does have space, since some slots have 2 gb bytes.
So the logic is not quite right. It just take care of the 12 count.
Joan
XPC_OBJ_LOB 68 509999 110772224 13522
68
XPC_OBJ_LOB 69 510002 110747648 13519
69
XPC_OBJ_LOB 70 510002 110747648 13519
70
XPC_OBJ_LOB 71 509999 110772224 13522
71
XPC_OBJ_LOB 72 509966 111042560 13555
72
XPC_OBJ_LOB 73 510002 110747648 13519
73
XPC_OBJ_LOB 74 510002 110747648 13519
74
XPC_OBJ_LOB 75 510002 110747648 13519
75
XPC_OBJ_LOB 76 509999 110772224 13522
76
XPC_OBJ_LOB 77 509975 110968832 13546
77
XPC_OBJ_LOB 78 509999 110772224 13522
78
XPC_OBJ_LOB 79 510002 110747648 13519
79
XPC_OBJ_LOB 80 510002 110747648 13519
80
XPC_OBJ_LOB 81 510002 110747648 13519
81
XPC_OBJ_LOB 82 318725 1677688832 204796
82
XPC_OBJ_LOB 83 318752 1677467648 204769
83
XPC_OBJ_LOB 84 318752 1677467648 204769
84
XPC_OBJ_LOB 85 318752 1677467648 204769
85
XPC_OBJ_LOB 86 286877 1938587648 236644
86
XPC_OBJ_LOB 87 255002 2199707648 268519
87
XPC_OBJ_LOB 88 255002 2199707648 268519
88
XPC_OBJ_LOB 89 255002 2199707648 268519
89
XPC_OBJ_LOB 90 255002 2199707648 268519
90
XPC_OBJ_LOB 91 255002 2199707648 268519
91
Prompt &&2 Segments that can not extend &&1 times given the free space:
select s.segment_name, s.tablespace_name, s.next_extent,
s.extents, decode(s.max_extents,2147483645,-1,s.max_extents) n1
from sys.dba_segments s, sys.dba_free_space fs
where s.segment_type in (
'TABLE','TABLE PARTITION',
'INDEX','INDEX PARTITION',
'LOBINDEX','LOBSEGMENT')
and s.tablespace_name <> 'SYSTEM'
and s.tablespace_name = fs.tablespace_name
and (s.next_extent *12 ) > (select max(fs2.bytes)
from sys.dba_free_space fs2
where s.tablespace_name =
fs2.tablespace_name)
group by s.segment_name, s.tablespace_name, s.next_extent,
s.extents, decode(s.max_extents,2147483645,-1,s.max_extents)
INTERSECT
select s.segment_name, s.tablespace_name, s.next_extent,
s.extents, decode(s.max_extents,2147483645,-1,s.max_extents) n1
from sys.dba_segments s, sys.dba_free_space fs
where s.segment_type in (
'TABLE','TABLE PARTITION',
'INDEX','INDEX PARTITION',
'LOBINDEX','LOBSEGMENT')
and s.tablespace_name <> 'SYSTEM'
and s.tablespace_name = fs.tablespace_name
and 12 > (select count(*)
from sys.dba_free_space fs2
where s.tablespace_name = fs2.tablespace_name
and s.next_extent <= fs2.bytes)
group by s.segment_name, s.tablespace_name, s.next_extent,
s.extents, decode(s.max_extents,2147483645,-1,s.max_extents)
order by 2;
exit
-----Original Message-----
Sent: Friday, April 06, 2001 9:03 AM
To: [EMAIL PROTECTED]
Well, if next_extent=250m, pctincrease=0. bytes *12 >= next_extent means
dba_free_space must have a (bytes*12) large space to satisfy the condition.
But I want 12 slots >250m, if I get less than 12 slots < 250m, I will get
email. Gee, I am confused myself, am I right?
Joan
-----Original Message-----
Kilchoer
Sent: Thursday, April 05, 2001 5:51 PM
To: Multiple recipients of list ORACLE-L
> -----Original Message-----
> From: Joan Hsieh [ mailto:[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]> ]
>
> 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.
What do you mean by "not 100% correct"? How do you know? Have you considered
the pct_increase factor for the next_extent?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Nicoll, Iain (metering)
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).
