| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: failed to extent to the next
Thanks, it finally works.
Joan
-----Original Message-----
Iain (metering)
Sent: Friday, April 06, 2001 2:00 PM
To: Multiple recipients of list ORACLE-L
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:Joan.Hsieh_at_mirror-image.com> ]
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')
from sys.dba_free_space fs2
where s.tablespace_name =
fs2.tablespace_name)
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')
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: ORACLE-L_at_fatcity.com
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:Joan.Hsieh_at_mirror-image.com
<mailto:Joan.Hsieh_at_mirror-image.com> ]
>
> 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: iain.nicoll_at_calanais.com 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: ListGuru_at_fatcity.com (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: Joan Hsieh INET: Joan.Hsieh_at_mirror-image.com 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: ListGuru_at_fatcity.com (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).Received on Fri Apr 06 2001 - 13:27:42 CDT
 |
 |