Rollback segments question (shrink)

From: Syltrem <syltrem_at_videotron.ca>
Date: Wed, 10 Jan 2001 16:08:13 -0500
Message-ID: <14476.41867$903.318784@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?
Do I have to shrink them manually once in a while?
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.
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 - 15:08:13 CST