Re: Q: local managed tablespace and tempfile

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:Qf_X9.31644$jM5.80740_at_newsfeeds.bigpond.com...
> Ted Chyn wrote:
> > questions:
> >
> > 1. would storage parameter in table definition override locally
> > managed tablespace in 8.1.7.
>
> No. But there's a subtlety. Suppose your tablespace has uniform extent
> sizes of 1MB, and your create table statement includes a storage clause
> that says "INITIAL 10MB NEXT 15MB".
>
> Then your table will be created with 10 1MB extents. So, in a sense, the
> storage clause at the table level has had some effect on the tablespace.
> But no, all extents must be of the uniform size.
>
> If your tablespace is autoallocate, the subtelty gets even subtler.
> Oracle allocates whatever size extents it deems appropriate. Usually,
> these start at 64K, then increase to 1M, 8M and 64M. But if your storage
> clause for the table starts out wanting 10M, it is likely that Oracle
> will skip giving you any 64K extents, and move straight on up to the 1M
> ones. Likewise, if you'd asked for an INITIAL of 200M, the tablespace
> would probably have allocated 4 64M ones.
>
> In any case, the NEXT extent is totally ignored. Going back to the first
> example, you've acquired 10 1M extents. If you now 'alter table blah
> allocate extent', then you will be given an 11th 1M extent. The fact
> that your storage clause asked for 15M is neither here nor there.
>

Just to clarify the above (because I just recently had to go through this with someone), the NEXT clause is only "totally ignored" *after* the segment as been initially allocated. If MINEXTENTS is > 1, then NEXT (and PCTINCREASE) are all used to calculate the initial size of the object, it's not based just on the INITIAL clause.

Richard Received on Tue Jan 28 2003 - 06:54:46 CST