Matthew Chappee (matthew@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: <->
<-> <->
<-> www.arikaplan.com <->
<-> <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
Back to Ari Kaplan's Home Page