Resize rollback segment

[sonaliak]

How do I increase the size of a rollback segment ?
I get following error.
ORA-01562: failed to extend rollback segment number 2 ORA-01650: unable to
extend rollback segment RBS02 by 256 in tablespace ROLLBACK
ORA-06512: at "A4.PR_WORFINDFX", line 138 ORA-06512: at line 1 Error Code
SQL or AFX: -1

Do I have to add a new rollback segment ?

[rsuri]

Increase the number of MaxExtents for the rollback segment.
If there is not enough space in the tablespace, then you will need to add a datafile as well.

- Rajeev

[Halo]

Another thing you can do is set Autoextend ON.

[jmodic]

Sonali,

Your ORA message is teling you there is not enough free space in your RB tablespace to extentend a rolback segment, so it has nothing to do with MAXEXTENTS. You should simply make more free space in your RB tablespace by one of the following:

a) add new datafile to tablespace
b) as Halo already sugested, you can set AUTOEXTEND for your current datafile(s) of RB tablespace (I personaly am not a big fan of AUTOEXTEND option, I've seen to many bugs and ORA-600 related to AUTOEXTEND)
c) resize (increase size of) you curent datafile(s)
d) if your rollbacks have grown and you don't have OPTIMAL parameter set for them, then SCHRINKing them (and setting OPTIMAL) might free a lot of space in your RB tablespace

HTH,