rollback segment full URGENT

[smacattack]

Hi,
I have 2 rollbacks online both in the same tablespace but with different size datafiles.
The smaller rollback has filled with:

ORA-1650: unable to extend rollback segment BIG_RB by 640 in tablespace
ROLLBACK_TS
Failure to extend rollback segment 9 because of 1650 condition
FULL status of rollback segment 9 set.

Although this filled it didn't continue onto the second larger rollback segment.

The First segment now shows full.
How can i force a switch and continue with the processing.
The alert logs shows no further activity but the processes time is still increasing.

Can i switch the rollback segments or am i doomed to abort and rerun with the larger rollback online only?


Many thanks

 

[DBAwhosaysNIE]

are you running 9? even if you are I don't think you can move a transaction to a different rbs, just resize/add datafile(s) to tablespace or fix storage clause (ex: maxextents unlimited) but I may be wrong.

I don't think you can do this with 8.x. if you're running 8.x (which is the scope of this board) it's probably already rolled back the last statement if not the whole transaction.

 

[SantaMufasa]

Smack,

By now, you may have resolved your problem one way or the other, but if it happens again, the suggestion from DBAwhosaysNIE (to just add more space) is a good, instant resolution.

In all versions of Oracle, a transaction lives its entire life in a single rollback segment...there is no "switching and continuing". The best method that I have found to avoid the error(s) you received is to set the datafile(s) that live under the RBS tablespace to AUTOEXTEND to 2GB each:

ALTER DATABASE DATAFILE '<full datafile name>'
AUTOEXTEND ON NEXT <some increment size like 50M>
MAXSIZE 2000M;

...and to set each rollback segment for "UNLIMITED" extents with appropriately sized OPTIMAL settings:

ALTER rollback segment <name>
storage (OPTIMAL <size> MAXEXTENTS unlimited);

As a result, we never incur the error you mention, and we run pretty sizable transactions, as well.

Let us know if you have questions or follow-on problems.

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:38 (26Mar04) UTC (aka "GMT" and "Zulu"), 14:38 (26Mar04) Mountain Time)