Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

migrated from stackoverflow.com Oct 13 '12 at 10:50

This question came from our site for professional and enthusiast programmers.

3 Answers

up vote 0 down vote accepted

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 )

share|improve this answer
    
This is a workaround at best - you wouldn't want to be doing this in production code... –  Robbie Dee Oct 12 '12 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 '12 at 15:43
    
Digging web: asktom.oracle.com/pls/apex/… –  danihp Oct 12 '12 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. –  FreshPhilOfSO Oct 15 '12 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 '12 at 7:54
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.