ORA-01562: failed to extend rollback segment number 11

Question

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

Answer 1

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 )

Answer 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.

Answer 3

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.