Unable to Extend Rollback Segment
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Unable to Extend Rollback Segment

  1. #1
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    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.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    SELECT name, rssize FROM v$rollstat a, v$rollname b where a.usn=b.usn

    and look which RBS is holding the space

  3. #3
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    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.

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well in that case it is your update that is too big because there is no rbs holding freespace

  5. #5
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    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)
    TarryBlogging
    --- Everything was meant to be---

  6. #6
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    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)
    TarryBlogging
    --- Everything was meant to be---

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Tarry he cannot extend not rollback segment not any performance problems! If someone has to reboot because of this ....

  8. #8
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    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.

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    it acquire 256K of course

  10. #10
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    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
  •  



Click Here to Expand Forum to Full Width