Re: rollbacks ??

There are four parameters:

N = number of rollback segments
E = extent size
O = optimal size
T = tablespace size

N: Set the number of rollback segments according to the number of concurrent transactions. Each segment can handle more than one concurrent transaction, but not all transactions are created equal. Check your stats for waits. Extra segments just reduce the size of your largest possible transaction. E: Set all initial and next extent sizes to the smallest multiple of 64 KB such that 500 * E > T - ((N - 1) * O). You don't want to run out of extents before you run out of space!
O: Set the optimal (shrink back) sizes such that most transactions do not cause rollback extends. This must be at least two extents. Check v$rollstat.

T: Set the total tablespace size for the largest transaction you have to process. If all the rollbacks are at their optimal size when your mega transaction starts, you will have T - ((N - 1) * O) available for the lucky segment.

There is no good way to calculate how much rollback a transaction will generate. It depends on the number of rows inserted, updated, dropped as part of the transaction. It also depends on how many of the columns are updated and the size of the rows deleted.

You now know that 505 * your current extent size is not big enough. I would double the extent size and try again. Be sure the tablespace is big enough!

Ed Lufker wrote:

> Hi all:
>
> Can anyone give me some tips on how to size my rollback segments
> for both batch and OLTP. I was getting the following error when doing a
> delete last night.
>
> ORA-1628: max # extents 505 reached for rollback segment R05
> Failure to extend rollback segment 6 because of 1628 condition
> FULL status of rollback segment 6 cleared.
>
> thanks
> eddie lufker
Received on Tue Jun 02 1998 - 12:27:24 CDT