Re: rollback maxextents

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 Received on Wed Oct 08 2003 - 14:59:14 CDT