RE: failed to extent to the next

From: Joan Hsieh <Joan.Hsieh_at_mirror-image.com>
Date: Fri, 06 Apr 2001 07:52:14 -0700
Message-ID: <F001.002E3FCE.20010406071528@fatcity.com>

<FONT face=Arial color=#0000ff

size=2>Jacques,
<FONT face=Arial color=#0000ff

size=2> 
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.
<FONT face=Arial color=#0000ff

size=2> 
<FONT face=Arial color=#0000ff

size=2>Joan
<FONT face=Arial color=#0000ff

size=2> 
<FONT face=Arial color=#0000ff

size=2>XPC_OBJ_LOB                            

68     509999  110772224     
13522         
68
 
<FONT face=Arial color=#0000ff

size=2>XPC_OBJ_LOB                            

69     510002  110747648     
13519         
69
 
<FONT face=Arial color=#0000ff

size=2>XPC_OBJ_LOB                            

70     510002  110747648     
13519         
70
 
<FONT face=Arial color=#0000ff

size=2>XPC_OBJ_LOB                            

71     509999  110772224     
13522         
71
 
<FONT face=Arial color=#0000ff

size=2>XPC_OBJ_LOB                            

72     509966  111042560     
13555         
72
 
<FONT face=Arial color=#0000ff

size=2>XPC_OBJ_LOB                            

73     510002  110747648     
13519         
73
 
<FONT face=Arial color=#0000ff

size=2>XPC_OBJ_LOB                            

74     510002  110747648     
13519         
74
 
<FONT face=Arial color=#0000ff

size=2>XPC_OBJ_LOB                            

75     510002  110747648     
13519         
75
 
<FONT face=Arial color=#0000ff

size=2>XPC_OBJ_LOB                            

76     509999  110772224     
13522         
76
 
<FONT face=Arial color=#0000ff

size=2>XPC_OBJ_LOB                            

77     509975  110968832     
13546         
77
 
<FONT face=Arial color=#0000ff

size=2>XPC_OBJ_LOB                            

78     509999  110772224     
13522         
78
 
<FONT face=Arial color=#0000ff

size=2>XPC_OBJ_LOB                            

79     510002  110747648     
13519         
79
 
<FONT face=Arial color=#0000ff

size=2>XPC_OBJ_LOB                            

80     510002  110747648     
13519         
80
 
<FONT face=Arial color=#0000ff

size=2>XPC_OBJ_LOB                            

81     510002  110747648     
13519         
81
 
<FONT face=Arial color=#0000ff

size=2>XPC_OBJ_LOB                            

82     318725 1677688832    
204796         
82
 
<FONT face=Arial color=#0000ff

size=2>XPC_OBJ_LOB                            

83     318752 1677467648    
204769         
83
 
<FONT face=Arial color=#0000ff

size=2>XPC_OBJ_LOB                            

84     318752 1677467648    
204769         
84
 
<FONT face=Arial color=#0000ff

size=2>XPC_OBJ_LOB                            

85     318752 1677467648    
204769         
85
 
<FONT face=Arial color=#0000ff

size=2>XPC_OBJ_LOB                            

86     286877 1938587648    
236644         
86
 
<FONT face=Arial color=#0000ff

size=2>XPC_OBJ_LOB                            

87     255002 2199707648    
268519         
87
 
<FONT face=Arial color=#0000ff

size=2>XPC_OBJ_LOB                            

88     255002 2199707648    
268519         
88
 
<FONT face=Arial color=#0000ff

size=2>XPC_OBJ_LOB                            

89     255002 2199707648    
268519         
89
 
<FONT face=Arial color=#0000ff

size=2>XPC_OBJ_LOB                            

90     255002 2199707648    
268519         
90
 
<FONT face=Arial color=#0000ff

size=2>XPC_OBJ_LOB                            

91     255002 2199707648    
268519         
91
 
<FONT face=Arial color=#0000ff

size=2> 
<FONT face=Arial color=#0000ff

size=2> 
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)INTERSECTselect 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;
 
<FONT face=Arial color=#0000ff

size=2>exit

  <FONT face=Tahoma
  size=2>-----Original Message-----From: Joan Hsieh   [mailto:Joan.Hsieh_at_mirror-image.com]Sent: Friday, April 06, 2001   9:03 AMTo: ORACLE-L_at_fatcity.comSubject: RE: failed to   extent to the next
  <FONT face=Arial color=#0000ff
  size=2>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?
  <FONT face=Arial color=#0000ff
  size=2> 
  <FONT face=Arial color=#0000ff
  size=2>Joan   

    <FONT face=Tahoma
    size=2>-----Original Message-----From: root_at_fatcity.com     [mailto:root_at_fatcity.com]On Behalf Of Jacques     KilchoerSent: Thursday, April 05, 2001 5:51 PMTo:     Multiple recipients of list ORACLE-LSubject: RE: failed to extent     to the next
    > -----Original Message----- >
    From: Joan Hsieh [<A
    href="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 <FONT
    size=2>> volume transactions going on. He want to detect the     > tablespace can't extent <FONT
    size=2>> 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? Received on Fri Apr 06 2001 - 09:52:14 CDT