Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: system tablespace extents question

Re: system tablespace extents question

From: Andy <>
Date: Wed, 24 Jul 2002 16:32:26 +0100
Message-ID: <gIz%8.135$>

"Jan Gelbrich" <> wrote in message news:ahmfip$u2fj8$
> > The second question is answered most easily - the storage clause
> > for the tablespace acts a default for all the storage values and the
> values
> > not specified when creating a table in that tablespace use these
> > Some values for a table may be specified and the remainder use the
> defaults
> > or all can default.
> This is what I thougth in the first place, too. It should be very easy to
> understand.
> But:
> If the extent default size in the tablespace is 100k, and a table is
> with 1M extent size,
> does this mean, that after some filled extents of 100k in the tablespace,
> one 1M extent may follow,
> and after it, again several 100k extents - so that the row of extents may
> have *different sizes* ?


Any one table will have extents of the same size if PCTINCREASE is set to 0, but it is possible to have tables having different sizes from one another even if both have PCTINCREASE of 0. This is how fragmentation can occur; imagine table a with uniform extents of 64K and table b with 1M extents. If the two are allocated alternately in the same datafile and then the small table is dropped the larger table cannot utilise this freed space as the "gaps" are too small.

Good practice is not to mix tables of wildly different extent sizes and I have always preferred to set PCTINCREASE to 0 and define a range of tablespaces each with successively larger default extent sizes. Tables would then be placed into the appropriate tablespaces. Clearly if you have tables which will grow logarithmically then PCTINCREASE of 0 won't work but usually this is not my experience. I currently use three tablespaces (small/medium/large) for tables and three for tablespaces. Seems to work as well as more fine grained approaches. This ties in nicely with locally managed tablespaces that Niall mentions in his response to your initial posting.

Hope this helps clear things up a bit

Andy Received on Wed Jul 24 2002 - 10:32:26 CDT

Original text of this message