Re: Rollback segments

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.

---
Name   : Lun Wing San
Title  : Oracle Application Developer of Hong Kong Productivity Council
         Oracle Database Administrator and System Administrator of QRC
Phone  : (852)27885841