Re: Why is NEXT_EXTENT changing all the time?

Well now (and hoping that I am reasonable and that the wind is in the west)

I disagree strongly with this suggestion.

For me ALL segments within a tablespace should have an identical extent size, and each segment should be allocated to a tablespace with an appropriate storage clause. This way a table which wants to grab a next extent can always do it (providing the tablespace is not full). In other words fragmentation is eliminated.

I utterly fail to see why one would size segments at different sizes, thus guaranteeing fragmentation, merely in order to hold one to three years worth of data. The only possible 'justification' would be in an environment when yearly reviews of database operation were the object. That translates to consultancy.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
"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

>