Re: Oracle Error when writing, deleting large amounts of data

Matthew Chappee (matthew_at_nospam.mattshouse.com) wrote:
: I get the following errors when writing or deleting large amounts of
: data. It's starting to get really annoying. What can I do to fix
: this?
: ORA-01562: failed to extend rollback segment number 8
: ORA-01628: max # extents (121) reached for rollback segment RB7
:
: Thanks,
: Matthew

Matthew,

There are a few things you can try. First, if it is possible in your situation, commit periodically so that the rollback segments never get too large.

If this is not feasible for you, then you can either enlarge all of your rollback segments or create one large rollback segment in addition to your other rollback segments. If you feel that many processes will benefit from larger rollbacks, then drop and recreate the segements with a much larger initial and next storage clause. Be sure to specify OPTIMAL size so that rollbacks have a chance to shrink back down.

The final option is to have your rollback segments as they are and add a large rollback segment to be used just for your large inserts and deletes. You will need at least 121 times the size as your other rollback segments. To use the rollback segment, before the load/delete you must specify:

SET TRANSACTION USE ROLLBACK SEGMENT segment_name;

When you do this, Oracle will force your session to use the larger rollback segment. Every time you do a COMMIT, ROLLBACK, or DDL-statement you must re-issue the above command.

Best of luck,

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 160+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             http://homepage.interaccess.com/~akaplan          <->
<->                                                               <->
<->             email: akaplan_at_interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->