RE: failed to extent to the next

[Joan Hsieh]

Title: RE: failed to extent to the next

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-----
From: Joan Hsieh [mailto:[EMAIL PROTECTED]]
Sent: Friday, April 06, 2001 9:03 AM
To: [EMAIL PROTECTED]
Subject: RE: failed to extent to the next

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-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jacques Kilchoer
Sent: Thursday, April 05, 2001 5:51 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: failed to extent to the next

> -----Original Message-----
> From: Joan Hsieh [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?