Maxextents unlimited on LMT ?

Hi all

I have a question for which I've never received an acceptable answer and for which I would love someone else's opinion.

Oracle insists that all objects that live in a locally managed tablespace have maxextents set to unlimited. I guess the question is why ?

I understand the space management advantages of using LMTs and that an object can quite possibly have many 1000s of extents with minimal to no performance issues. As performance isn't effected, why then have maxextents artificially lowered I generally hear is the argument.

However maxextents was not set solely for performance reasons, another key reason is space management and capacity management reasons. I might very well wish to set a table with maxextents to (say 50) not because of concerns over performance but concerns that I might not have sufficient space for over 50 extents. In cooperation with the developers and users, I'm expecting x amount of space for this particular object (and indeed all other objects in a tablespace). If we have grossly underestimated the space by a factor of x, I may very well want an object to stop extending until the issue is resolved. I might have an application going ballistic and inserting data madly, I may well want the object to stop extending rather than fill the tablespace. I have a rollback segment with a blocking transaction, I may well want it to stop it extending rather than fill the tablespace. Etc.

However, with a locally managed tablespace I have no such control. The maxextents storage clause is ignored and I'm not terribly sure why.

Any suggestions ?

Richard Received on Wed Jul 03 2002 - 06:21:28 CDT