Newsgroups: comp.databases.oracle.misc
Subject: Re: ROLLBACK SEGMENTS: Some basic questions
References:
>1. Is there a way to have a transaction not use any rollback segment ?
>I am more concerned about performance and data loss is not really a
>concern.
>2. I have a rather complicated PL/SQL script that works on a table that
>has 140,000 rows in it. The script is deleting rows based on a set of rules.
>The process after 30 minutes aborts and reports the following :
>BEGIN
>*
>ERROR at line 1:
>ORA-01562: failed to extend rollback segment number 1
>ORA-01628: max # extents (100) reached for rollback segment R01
>I thought I had taken care of all my rollback segment blues when I created
>the following rollback segment :
>create public rollback segment r01 tablespace RBS
>storage (initial 500K NEXT 500K OPTIMAL 10M MINEXTENTS 15 MAXEXTENTS 100) ;
>I dropped all the other rollback segments intentionally to help me debug
>this problem. I am now thinking that maybe I should increase the maxextents
>to 200 or some such number and maybe add another rollback segment with
>identical settings. But I am not sure if that is going to solve my problem.
>Any ideas ?
>Atif Khan
-- Note: My answers are interspersed below ... --
aak2@Ra.MsState.Edu (Atif Ahmad Khan) writes:
>create public rollback segment r01 tablespace RBS
>storage (initial 500K NEXT 500K OPTIMAL 10M MINEXTENTS 15 MAXEXTENTS 100)
The reason that your transaction failed is that your INITIAL/NEXT sizes
are too small for what you are trying to do. Since you ran out of extents
(100 maximum), you need more than 500kx100=50M of rollback to complete
your transaction.
Unfortunately, your statement failed and so it is impossible to tell how
much rollback you truly need. The best solution is to recreate the
rollback segment with:
CREATE PUBLIC ROLLBACK SEGMENT R01 TABLESPACE RBS
STORAGE (initial 10M NEXT 5M OPTIMAL 20M MINEXTENTS 3 MAXEXTENTS
100);
If you still wanted MINEXTENTS to be 15, you can decrease the initial and
next accordingly.
Run the query again and monitor how much rollback space was used. With
MAXEXTENTS 100, you will have 505M to use. Be sure that your tablespace is
large enough as well.
One final note...if you have an environment where there are both large
transactions/reports (such as the one you describe) AND short
transactions, then make several small rollback segments and one or
more large rollback segment(s) such as the one I described. For the
large transactions/reports, you can say (after COMMIT or ROLLBACK):
SET TRANSACTION USE ROLLBACK SEGMENT RB_LARGE01;
Hope this helps...
-Ari Kaplan
Independent Oracle DBA Consultant
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> Visit my Web Page: www.arikaplan.com <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
Back to Ari Kaplan's Home Page