Re: Rollback Segments

If you have long running transaction than it would be better to have bigger rollback segments. I have only 5 rollback segments but I had to increase its storage parameters (because of "snapshot too old error") an set them to: INITIAL = 500K
NEXT = 500K
Since that change I haven't seen "snapshot too old" error.

1. If you want rollback segments to be automatically brought online after the database start, you have to add segment's name to the ROLLBACK_SEGMENT parameter in database's init.ora file (at the beginning of file).
2. If you haven't specified the OPTIMAL parameter then the rollback segment's extents will not be realeased - rollback segment will grow up to MAXEXTENTS value. If you want to set this parameter and you have long running transactions in your database than OPTIMAL should be large so that Oracle does not have to shrink and allocate extents frequently (it consumes resources) and to avoid "snapshot too old" error. I don't use OPTIMAL parameter and when rollback segments are too big I simply use "ALTER ROLLBACK SEGMENT rollback_segment_name SHRINK TO integer K (or M)".
3. When you increase rollback segments's size you'll have to add datafile(s) to the rollback segment tablespace. If you don't then soon you'll see "unable to extend rollback segment .... by .... in tablespace ...." error

Hope this helps
Peter

calberto_at_my-dejanews.com wrote:

> Hi all!
>
> I'm having problems with rollback segments in Oracle. We, in our company, have
> an long-running application (about 3 hours) which after 2 hours give us the
> following message :
>
> ORA 01555 - Snapshot too old .....
>
> The server currently has 16 rollback segments, which one with the following
> parameters :
>
> INITIAL - 50K
> NEXT - 50K
> OPTIMAL - DEFAULT (I suppose Oracle's default is NULL)
> MINEXTENTS - 2
> MAXEXTENTS - 121
>
> Is the solution increase the NEXT parameter?
>
> Beyond this, I have some doubts :
>
> 1) I have manually put all these segments online, but when the machine
> rebooted (for other reasons), Oracle have put some of them offline. Why?
>
> 2) Since my OPTIMAL parameter is the default, when Oracle will release the
> extents of the segments?
>
> 3) All these rollback segments are in one tablespace which has 2 datafiles of
> 20 MB each, but there is only 2 MB free. Is this related with the problem?
>
> We have Oracle 7.3 running on NT 4.0.
>
> Thank in advanced,
>
> Carlos
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
Received on Tue Aug 18 1998 - 06:45:33 CDT