Re: Rollbacksegments

"Mechthild Marten" <memarten_at_uos.de> wrote in message news:acrpod$17o$1_at_newsserver.rrzn.uni-hannover.de...
> Thanks for your answer.
> What I mean is this.
> Oracle accepts the following sql-statements without an error message:
>
> create rollback segment test storage (initial 1M, next 1M, minextents 4)
> alter rollback segment test shrink to 1M
>
> What happened with the initial parameters.

What happened with the shrink command is more like it. Did you check the size of the rollback segment after doing this command? Guess what: it won't be 1M. As per your create statement, it will still be 4M, because a shrink won't cause the segment to have less extents than MINEXTENTS.

The shrink command is failing silently, therefore. You need to query v$rollstat to see what (if anything) it is actually doing.

Regards
HJR
>
> Regards
> Mechthild
>
>
> Howard J. Rogers schrieb in Nachricht ...
> >
> >"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 Mon May 27 2002 - 16:36:04 CDT