0

I´m trying to execute this query into a SP

 DELETE FROM table SC
      WHERE SC.evaluationMonth= 'Some month';
 COMMIT;

But for this month there are something like 10 million records. So I get the error

ORA-01562: failed to extend rollback segment number 11.

How can I improve the SP or make something to solve this problem?
Pdta: I had increase de unto to 20G

3 Answers 3

0

When you delete data, oracle save delete data because perhaps transaction don't ends with commit and oracle need to restore delete data.

This copy of data is stored in rollback segments. In Oracle settings you have max extends for this segments.

Solution

  • You can extend segments
  • You can delete data by 'slices':

Sample spliting delete data:

 DELETE FROM table SC
 WHERE SC.evaluationMonth= 'Some month' 
 and SC.evaluationDay= '1';
 COMMIT;

 DELETE FROM table SC
 WHERE SC.evaluationMonth= 'Some month' 
 and SC.evaluationDay= '2';
 COMMIT;

 ...

 DELETE FROM table SC
 WHERE SC.evaluationMonth= 'Some month' 
 and SC.evaluationDay= '31';
 COMMIT;

(you can do a loop, of course )

5
  • This is a workaround at best - you wouldn't want to be doing this in production code...
    – Robbie Dee
    Oct 12, 2012 at 14:37
  • If you need operation in a single transaction for isolation reasons then you should increase rollback limits. If you don't need a single transaction then delete slice by slice, put code in a stored procedure. What is the problem to move this to production? This will be more fast and less locks.
    – danihp
    Oct 12, 2012 at 15:43
  • Digging web: asktom.oracle.com/pls/apex/…
    – danihp
    Oct 12, 2012 at 15:45
  • This solution won't be faster - it might be significantly slower, as it could quite easily result in the same blocks being written back to disk multiple times.
    – Philᵀᴹ
    Oct 15, 2012 at 7:39
  • @Phil, thanks about your comment! Please, post your own answer in order to help OP and other users with this issue. Also, please, alert me and OP when post was published to learn alternate solution. Thanks!
    – danihp
    Oct 15, 2012 at 7:54
2

This isn't a coding issue - the rollback segment simply isn't big enough. Speak to your DBA about getting it extended.

Another issue could be other long running jobs hogging the available rollback space. Either way, the DBA should be your first port of call.

0

either allow more space for the rollback segment to grow.

or

delete fewer rows at a time between commits. -so make a loop and change your where clause to reduce the number of affected rows.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.