Unable to Extend Rollback Segment

Hi,

When Issuing an update statement I am getting the following error

ERROR at line 1:
ORA-01562: failed to extend rollback segment number 5
ORA-01650: unable to extend rollback segment RB2 by 32 in tablespace RBS


The Rollback segment is as follows

RBS - TableSpace - Initial =1M, Next = 1M Maximum value = unliminted

RBS Datafile = Size = 500MB (Used = 4MB)

Rollback segments = RB0 , RB01,RB02,RB03,RB04,RB05,RB06,RB07
For all of them
(Extents )Initial = 256K,Next = 256K, Optimal = 512K

Minimum Number = 2, Maximum Number = 32765

As I see there is a lot of space to extend then why is it giving the error

Thanks

SELECT name, rssize FROM v$rollstat a, v$rollname b where a.usn=b.usn

and look which RBS is holding the space

Quote:

---

Originally posted by pando
SELECT name, rssize FROM v$rollstat a, v$rollname b where a.usn=b.usn

and look which RBS is holding the space

---

This is what i get

SQL> SELECT name, rssize FROM v$rollstat a, v$rollname b where a.usn=b.usn
2 /

NAME RSSIZE
------------------------------ ----------
SYSTEM 401408
RB0 565248
RB1 565248
CTXROL 237568
RB2 565248
RB3 565248
RB4 565248
RB5 565248
RB6 565248
RB7 565248

well in that case it is your update that is too big because there is no rbs holding freespace

Create a few more rb segments,since you have enough space i guess you have more transactions than the rbs might consider handling..
alternatively query V$waitstat to query if there are any waits particularly in undo header, undo block etc..

or restarting the instance will also reset the dba_rollback_segs parameters.......

Check also you optimal clause, depending upon the shrinks,and aveshrinks parameters, you need to adjust them.
if shrinks are low and aveshrink high then the setting is good.
If aveshrink is low and closer to the current setting then it's fine if not the optimal is too high.

Quote:

---

Originally posted by Tarry
....Check also you optimal clause, depending upon the shrinks,and aveshrink parameters, you need to adjust them.

---

sorry i meant it(the optimal setting)

Tarry he cannot extend not rollback segment not any performance problems! If someone has to reboot because of this .... :o

In my case the Rollback tablespace has Initial and Next set to 1 MB and in case of Rollback segments its 256K

Will the database try to acquire 1 MB for every Rollback segment extension because the tablespace next is 1 MB or it will still acquire 256 KB.

Please suggest.

Thanks

it acquire 256K of course

Can somebody throw more light on how to size rollback segments .

I dont understand your question, are you asking how to size or you mean why you hit the error....?

Now I know why I hit the error... Thanks for the help.

The question was about basics in sizing Rollback segments and things to keep in mind.

Sizing RBS is an iterative process.

Query from v$rollstat when the db has high volume of transactions.
The aveactive column value will be the starting size (for initial and next) for the RBS.