Re: rollback segment with no optimal question

Thank you anna, Vc and hjr for your help.

YEs there is some update activity in the database. I said it is OLTP system. So mostly the update is 1 to 10 rows max.

Thing is this report is run many times a day and it is important and has to run successfully. We have increased the size and number but cannot decide how much. And the error keeps occurring. Initially we had optimal set then we increased it and then removed it. Thank you.

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<3fbfb764$0$13498$afc38c87_at_news.optusnet.com.au>...
> "anu" <anu_d_77_at_yahoo.com> wrote in message
> news:ba155ca5.0311221011.3a55fb28_at_posting.google.com...
> > Hi,
> > I have rollback segment with no optimal set. the rollback tablespace
> > has a lot of space. It is a OLTP system with many but small
> > transactions. So I expect no shrinking. However for one report that
> > runs around 20 minutes we are getting ora-01555 snapshot too old. the
> > intial and next are set to 1M which I think is OK for OLTP transaction
> > size. There are many rollback segments = 40. So how do I go about
> > increasing the size or the number and on what basis? Thank you
>
> Well, to increase the number, it's just a question of creating more of them,
> and making sure they are online and are brought online at each startup:
>
> create rollback segment X tablespace Y;
> alter rollback segment X online;
>
> and in the init.ora, change
>
> ROLLBACK_SEGMENTS=A,B,C
>
> to
>
> ROLLBACK_SEGMENTS=A,B,C,X
>
> There is no way to make an existing rollback segment bigger really. Best
> thing would be to drop them and re-create them:
>
> alter rollback segment A offline;
> drop rollback segment A;
> create rollback segment A storage (initial 5M next 5M minextents 20);
>
> Or whatever values are appropriate, of course. And you have to do it one by
> one.
>
> A variation on this theme stems from the fact that I dislike seeing storage
> clauses specified at the segment level. The default storage clause of the
> tablespace itself should be doing the job. Therefore:
>
> 1. Edit init.ora so that
>
> ROLLBACK_SEGMENTS=A,B,C
>
> is changed to
>
> #ROLLBACK_SEGMENTS=A,B,C
>
> 2. Shutdown immediate
> 3. Startup restrict
> 4. Drop all rollback segments
> 5. Drop the rollback segment tablespace
> 6. Remove the O/S file from disk
> 7. Re-create the rollback segment tablespace with a better default storage
> clause
> 8. Re-create all rollback segments with the simple command 'create rollback
> segment A tablespace Y' (ie, no storage clause)
> 9. Shutdown immediate
> 10. Edit the init.ora, and re-instate the ROLLBACK_SEGMENTS line
> 11. Startup.
>
> Regards
> HJR
Received on Mon Nov 24 2003 - 00:13:46 CST