-
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
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
SELECT name, rssize FROM v$rollstat a, v$rollname b where a.usn=b.usn
and look which RBS is holding the space
-
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
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
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.
Tarry Singh
I'm a JOLE(JavaOracleLinuxEnthusiast)

--- Everything was meant to be---
-
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 Singh
I'm a JOLE(JavaOracleLinuxEnthusiast)

--- Everything was meant to be---
-
Tarry he cannot extend not rollback segment not any performance problems! If someone has to reboot because of this ....
-
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
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
it acquire 256K of course
-
Can somebody throw more light on how to size rollback segments .
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|