Re: rollback maxextents

In fact, upgrade to 9i and use automated UNDO and start living!!! No need to worry about maxextents or practically anything else in the database for undo.

HTH,
Brian

"Howard J. Rogers" wrote:
>
> Charles J. Fisher wrote:
>
> > On Wed, 8 Oct 2003, Sybrand Bakker wrote:
> >
> >> >I am also unable to modify MAXEXTENTS for these rollback segments.
> >> It is a LMT, isn't it. MAXEXTENTS are not applicable to LMTs
> >
> > Thanks; I didn't realize that LMTs were limited in this way.
> >
> > I found a workaround, but it seems pretty ugly to me:
> >
> >
> http://www.faqchest.com/prgm/oracle-l/ora-03/ora-0301/ora-030106/ora03010616_24325.html
> >
>
> Why do you care about maxextents in the first place?
>
> Limiting the number of extents that an object (any object) could acquire
> made sense when each extent was represented by a row in a data dictionary
> table... lots of extents, lots of rows... bloat on the data dictionary,
> performance impacts from working with a bloated data dictionary.
>
> But in LMTs, there are no rows in the data dictionary to worry about. And
> hence the number of extents ceases to be a worry.
>
> Presumably, you worry that a rollback segment will grow large, and you want
> to stop that happening? Again, why? If the rollback segments are sized
> appropriately in the first place, normal transactional activity will not
> require the rollback segments to grow. (And on that note, your creation of
> these segments with just two extents is not good practice. Rollback
> segments should *start* big enough to house the transactions you are about
> to fire at them. Otherwise, your transactions will wait whilst they arrange
> the growth of the rollback segments).
>
> Of course, a rogue user can leave a transaction uncommitted, and you'll get
> growth as a result of that. But you can never totally rule out that
> possibility, and maxextents is a bit of a blunt instrument when trying to
> do so: it will kill legitimately large transactions as well as prevent
> growth caused as a result of blocking transactions.
>
> So if a blocking transaction causes growth, let it grow. You have optimal
> (not a particularly good way of dealing with it, though) and 'alter
> rollback segment X shrink to 16M' statements to deal with that situation
> after the event. You don't, in other words, *need* maxextents.
>
> Besides, you have a form of maxextents in the fact that the tablespace is of
> a fixed size (at least, I *hope* it's of a fixed size and doesn't have
> autoextend set). That places an absolute limit on the amount that you
> rollback segments can grow.
>
> So the question remains: why do you feel the need to set maxextents?
>
> I wouldn't, anyway.
>
> Regards
> HJR

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"