Re: Rollback segments

In article <32AFCE00.7D48_at_qrcsun.qrc.org>, "Lun Wing San (Oracle)" <wslun_at_qrcsun.qrc.org> writes
>Kochis wrote:
>>
>> Trying to delete all the data from a table (6000 records)
>> I recieved the following:
>>
>> Ora - 1562: Failed to extend rollback segments (Id=1)
>> Ora - 1682: Max # extents (121) reached for rollback segment add_rb
>>
>> Did I run out of extents and I need to increase max?
>> Did I run out of space in the rollback segment? (same as above?)
>>
>> Also,
>> I have three rollback segments created and online. Oracle always seems to
>> use the first one i created? Should I point it to a different one?
>
> For the errors, it was because the size of rollback segment is not large
>enough. If
>121 is really the limit of your operating system, you need to drop the rollback
>segment
>and create a new one with a larger INITIAL storage parameter. On the other hand,
>if 121
>is not the limit, you can just alter table storage (MAXEXTENT upper_limit).
>
> Alternatively, if you have a large size rollback segment, you can specify to
>use it
>for your transaction as SET TRANSACTION USE ROLLBACK SEGMENT
>larger_rollback_segment at
>the beginning of your transaction. Rollback segment is used in round-robin
>fashion-like.

A maximum of 121 extents is (prior to 7.3) I belive related to the block size. The first block of a table index rollback segment etc. stores the extent map. Thus there is a limited abount of space in this first block to store the map.

As from 7.3 Oracle have now got over this by using the last block as another extent map if furter extension is required.

On a recent Oracle Course I attended, The tutor advised setting Min and Max extents to the same value for a Rollback Segment as you do not really want Rollback Segment dynamically growing as this has a performance overhead.

Other than that I aggree with the comments regarding Rollback Segments.

--
Neil Roberts

The views expressed above are my own, and are not representative of Datasure
Associated or British Gas TransCo