Re: Rollbacksegments

"Mechthild Marten" <memarten_at_uos.de> wrote in message news:acqgvq$lbp$1_at_newsserver.rrzn.uni-hannover.de...
>
> Hi,
> can everybody tell me about physical memory allocation for
rollbacksegments.
> I don't understand what happens.
> When I create rollbacksegments with initial, next, min, max extent
> parameters it is not possible to create a new one if the min extents value
> of all rollbacksegments oversize the physikal memory of the datafile for
the
> rollbacksegments.

Not entirely sure I understand the question, but here goes for nothing. Let's assume you have a 10M tablespace (and that all 10M are available for use).

If your create rollback segment command looks like this:

create rollback segment blah storage (initial 1m next 1m minextents 4);

... then you'll be able to create 2 rollback segments. Minextents means that the segment acquires 4 extents from the word go, each of 1M, which means each segment is 4Mb big. Therefore, 2 segments chews up 8Mb, and 2Mb are left over. At this point, trying to create a third segment with the same SQL command would produce an error.

As you use rollback segments, however, they can grow. Suppose someone now starts a long transaction which generates 6Mb of rollback. As the transaction starts, Oracle decides to place its rollback in one or other of your two rollback segments. Once a transaction starts in a rollback segment, it must finish there. So the segment gets progressively fuller and fuller, as the transaction proceeds. When the segment is full (ie, when the transaction has generated 4Mb of rollback), it has to grow to accomodate the continuing transaction. It will acquire another extent -an additional 1Mb. And then another one... at which point, this segment is now 6Mb big, and the tablespace is full.

Left to its own devices, the segment will hold on to its additional 2Mb for ever. However, you can create a rollback segment with the keyword "OPTIMAL", which sets a target size for the segment. That's generally a bad idea (for reasons often discussed on this group), but it would mean (for example) that the 6Mb segment might automatically shrink back to its original 4Mb -at which point you get the 2Mb back again as free space.

Or you can issue the 'alter rollback segment shrink to 4Mb' command to make the shrink happen at a time of your choosing. But you still get the 2Mb back again.

During a shrink (either an automatic one induced by OPTIMAL, or a manual one), the segment guarantees never to drop extents which contain active transactions' undo. So you might not get the entire 2Mb back, but as much as it is possible to get back without seriously inconveniencing existing transactions.

Add in a third consideration. Suppose when you created the rollback segment tablespace, you'd added the line 'autoextend on next 1Mb'. That means the *datafile* will grow to accomodate any growth of the segments. When you shrink a segment, the datafile does *not* shrink.

So say that long-running transaction actually needs to write 8Mb of rollback. It starts in a 4Mb segment. It causes the segment to grow to 6Mb. The tablespace is now full, but the transaction needs to write 2 more Mb of rollback -so the datafile grows in two 1Mb increments to become 12Mb big. The transaction completes. You shrink the rollback segment to 4Mb. You now have two 4Mb rollback segments again, but in a 12Mb tablespace -because the datafile didn't shrink. You can now issue a third 'create rollback segment' command like the first one, and it will work: there *is* a spare 4Mb of space to accomodate the requested 'minextents 4'.

Hope that helps
Regards
HJR
>
> On the other side I can shrink the existing rollbacksegments without an
> error message. Afterwards it is possible to create an new one. My
question:
> What happened with the initial rollback segments paramenters?
>
> Thanks already,
> Mechthild
>
>
>
>
>
>
>
>
Received on Sun May 26 2002 - 15:53:30 CDT