Re: Why is NEXT_EXTENT changing all the time?

Daniel A. Morgan <dmorgan_at_exesolutions.com> wrote in message news:3A6FBBA2.4F17BAF2_at_exesolutions.com...
> > I am trying (hard!) to get same size extents throughout my tablespace,
 but
> > Oracle *8.0.5.1) won't let me do it.
> >
> > NEXT_EXTENT keeps changing for about 20 tables (out of 1500). Please
 someone
> > have a look at the following sequence and tell me what I do wrong or do
 not
> > understand!
> > Note: I picked a bad example maybe (numbers are look alike)
> > I want extent size = 160 (one-sixty) and Oracle changes for 168
> > (one-sixty-eight)
> > a) table created with extent=160k
> > b) import data
> > c) extent now 168
> > d) truncate table
> > e) extent back to 160
> >
>
> I can not imagine why it is happening unless you have pctincrease set to a
> non-zero value.. But I can not imagine why you would want the EXTENT sizes
 to be
> the same. That is pretty much the definition of a bad design.
>
> Reasonable people will disagree on this but for myself, initial extents
 should
> be sized to hold, as near as possible, 1 to 3 years worth of data
 depending on
> the application. Next extents are generally sized, again depending upon
> anticipated table growth, at between 25% and 100% of initial extent size.
 And
> please please set PCTINCREASE to ZERO for all tables and indexes.
>
> Daniel A. Morgan
>

This is terrible advice, sorry Daniel (and I do try to be reasonable most of the time). Your initial extents will be largely fresh air for much of your three years, and you are practically guaranteeing fragmentation... As one who has shares in Seagate, perhaps I should encourage you to keep up the good work! But apart from anything else, if you look which way the wind is going, by about Oracle 10 (OK, maybe Oracle 15) you won't even be able to specify odd extent sizes -locally managed tablespaces with uniform extent sizes clearly being the path that Oracle is mapping out for the future.

Regards
HJR Received on Thu Jan 25 2001 - 15:40:51 CST