-
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
-
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?
-
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 ???
-
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?
-
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
-
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?
-
SORRY I MISTYPED ON THE NEXT EXTENT
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|