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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I´m trying to execute this query into a SP

      WHERE SC.evaluationMonth= 'Some month';

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

migrated from Oct 13 '12 at 10:50

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

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.


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

Sample spliting delete data:

 WHERE SC.evaluationMonth= 'Some month' 
 and SC.evaluationDay= '1';

 WHERE SC.evaluationMonth= 'Some month' 
 and SC.evaluationDay= '2';


 WHERE SC.evaluationMonth= 'Some month' 
 and SC.evaluationDay= '31';

(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:… – 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. – Phil 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

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

either allow more space for the rollback segment to grow.


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

Your Answer


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