DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: ORA-01628: max # extents (1010) reached for rollback segment LARGE_RBS

  1. #1
    Join Date
    Sep 2002
    Posts
    411

    ORA-01628: max # extents (1010) reached for rollback segment LARGE_RBS

    SQL> DELETE FROM BLAH WHERE GRID_LVL>3;
    DELETE FROM BLAH WHERE GRID_LVL>3
    *
    ERROR at line 1:
    ORA-01562: failed to extend rollback segment number 1
    ORA-01628: max # extents (1010) reached for rollback segment LARGE_RBS

    CAN SOMEONE TELL ME WHAT HAPPEN AND HOW TO FIX THIS ERRORS???

    THANKS

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Gee, can't you read the error text? It tells you exactly what happened:
    max # extents (1010) reached for rollback segment LARGE_RBS
    Your rolback segment was created with the MAXEXTENTS limited to 1010. Now your delete transaction has caused thi rollbac ksegment to extend over all those 1010 extents, yet this wasn't enough - the transaction required for an additional extent (1011th) end hence it failed.

    How to fix it? Either redefine your rollback segment(s) to accomodate such a large transaction or perform your delete in smaller transactions.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Sep 2002
    Posts
    411
    Jmodic,

    thanks for your reply, I did read the error and I tried to fix it once. The original MAXEXTENT was 505 and it bomb so I increase it to 1010 and it still bomb and I thought that there is a trick someone might to to fix this problem so that I posted it in here.

    Any way, if I can not have my delete in smaller transaction so I have to increase the MAXEXTENTS but to what value ???

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Well, if you've can afford this rollback to take as much space as it needs (or as much as there is available in its tablespace), then why not set MAXEXTENTS to UNLIMITED? You can allways shrink it back when the transaction is finnished and reset its maxextents to whatever you want. BTW, what are the INITIAL and NEXT settings for this rollback segment?

    Anyway, can you tell us how many records (out of how many in total) are you deleting with this transaction? Perhaps it would be faster (and less RBS space demanding) to insert those records that are not to be deleted into an interim table, truncate your table BLAH and reinsert the remaining records from the interim table.

    Also, indexes can contribute extensively to the required rollback space needed. If you can afford, it might help if you drop (at least some of) the indexes on your table and recreate them after the delete is done.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Sep 2002
    Posts
    411
    THANKS FOR THE ADVISE.

    I WILL CREATE A DUMMY TABLE WITH THE DATA I NEED AND TRUNCATE ALL OF THE DATA ON THIS TABLE

    BTW , THE INITIAL EXTENT IS 10240 AND THE NEXT EXTENT IS 40 AND THE OPTIMAL IS SET TOO UNLIMITED

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by mike2000
    BTW , THE INITIAL EXTENT IS 10240 AND THE NEXT EXTENT IS 40 AND THE OPTIMAL IS SET TOO UNLIMITED
    Is this a typo or what? NEXT = 40 ????? That's 40 bytes! No wonder it filled those 1010 extents, that is barely a bit more than 1 MB space alltogether for that so-called "LARGE_RBS" !!!!!!

    And OPTIMAL set to UNLIMITED ??? You realy need to read about rollback segments in Concepts and/or Administration guide to undertand at least the very basic things about rollback segments.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Sep 2002
    Posts
    411
    SORRY I MISTYPED ON THE NEXT EXTENT

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    10k initial.... sigh

    I think your company should consider use SQL Server, no kidding at least you dont need knowledgble people to maintain and you seems like you dont need Oracle power neither

  9. #9
    Join Date
    Jan 2001
    Posts
    3,134
    Originally posted by pando
    10k initial.... sigh

    I think your company should consider use SQL Server, no kidding at least you dont need knowledgble people to maintain and you seems like you dont need Oracle power neither
    Always inspirational, supportive and politically correct.

    Good job "Moderator"

    MH
    I remember when this place was cool.

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