Re: Maxextents unlimited on LMT ?

Richard Foote wrote:
>
> 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

I would say that the closest approximateion you could get would be tablespace quotas, but of course that would typically require lots of forethought and lots of tablespaces to get close to controlling this.

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."