Re: Rollback segments question (shrink)

Answers embedded

Hth,

Sybrand Bakker, Oracle DBA

"Syltrem" <syltrem_at_videotron.ca> wrote in message news:14476.41867$903.318784_at_weber.videotron.net...
> Hi!
>
> Let's say I have a tablespace total size 100M.
>
> Inside it 2 rollback segments, R01 and R02 with INITIAL=NEXT=10M
> MINEXTENTS=2 MAXEXTENTS=UNLIMITED OPTIMAL=20M.
>
> One transaction starts using R01 and allocates 6 extents.
> Transaction finishes. Rollback segment R01 not automatically shrunk by
> Oracle. That leaves 20M free in the tablespace (6 allocated to R01, 2 by
> R02)
>
> Another transaction starts, using R02. It also requires 6 extents. It
 bombs
> out.
>
> It "looks" like I have enough space in the rollback segment, but the
 problem
> is that it is never de-allocated. I have to manually do a ALTER ROLLBACK
> SEGMENT R01 SHRINK to free it and keep transaction 2 from crashing.
>
> * When are the rollback segments shrunk, by default?
If you mean automatically: unless you set the OPTIMAL clause on the rollback segment never.

> * Do I have to shrink them manually once in a while?

Doing so increases the chance of getting ORA-1555 snapshot too old

> * Is it a good idea to have MAXEXTENTS UNLIMITED? I do this because
> sometimes users do huge transactions and this is unpredictable - they use
 a
> paackage and I can't easily direct big transactions to a specific rollback
> segment.

I usually for this particular type of segment configure maxextents so the total size to be allocated in the tablespace *exactly* equals the size of the tablespace.
If you have maxextent unlimited for this particular segment type keep in mind having many extents for rollback segments is not necessarily good: extent management should be kept down to a minimum or you need a locally managed tablespace. You may also consider to have the tablespace autoextend: IMO maxextents unlimited will only *shift* the problem not resolve it.

> * Never mind the numbers if you don't like them, it's just for the
 example!
>
> Thanks!
>
> Syltrem
> http://pages.infinit.net/syltrem (OpenVMS Web Site)
>
>
>
>
>
>
Received on Wed Jan 10 2001 - 17:15:26 CST