Re: rollback segment keeps growing

In article <82m028$bhp$1_at_nnrp1.deja.com>,   arnaud.riess_at_uhp.u-nancy.fr wrote:
>
> > There are a couple of things you can do:
> > 1) Set you optimal parameter which should probably equal you next
> > extents X minimun extent number and your initial and next extent
sizes
> > should match for rbs segments
> >
> > This will provide automatic release of unused extents
> >
>
> So if you don't specify an optimal size, your rollback segments are
> never released even when there isn't any active transaction ? What
> happens when they're all used after a while ?
>
> Arnaud.
>

New transactions allocated to the segment can use the allocated extents if necessary, but because Oracle is not releasing unused extents in excess of the minimum number that must always be allocated then the available free space in the tablespace may become insufficient to support extending a segment that needs another extent. This can be true even though 90% of your allocated extents are unused.

In the old days (couple of years ago) it was sometimes necessary to drop and recreate the rbs segments. Depending on how large a transaction your system had to be able to handle in any rollback segment and your total available rbs space you could end up with every segment extending until all free space was consumed. One way around this was to set every rbs segment to its maximum extent size and if a transaction died with a max-extents error then it had to be rewritten to commit more often. But if you could not assign the transactions to specific segments, and required more segments than could be fit into the tablespace at max-extents size then you had to do maintenance on your rbs segments just like you did tables and indexes.

I think optimal first appeared with 7.1 so the above was true with versions 6 and 7 and probably before.
--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Dec 09 1999 - 15:31:00 CST