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:
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.Quote:
max # extents (1010) reached for rollback segment LARGE_RBS
How to fix it? Either redefine your rollback segment(s) to accomodate such a large transaction or perform your delete in smaller transactions.
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.
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
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" !!!!!!Quote:
Originally posted by mike2000
BTW , THE INITIAL EXTENT IS 10240 AND THE NEXT EXTENT IS 40 AND THE OPTIMAL IS SET TOO UNLIMITED
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.
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
Always inspirational, supportive and politically correct.Quote:
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
Good job "Moderator" :)
MH