Re: logical storage philosophy?

This is one of those old wives' tales that should have died years ago.

Setting the tablespace default pctincrease to anything other than 0 is asking for trouble. Setting explicit storage clauses on data segments is asking for trouble.

Since 7.3.3 (or thereabouts) Oracle has had the MINIMUM EXTENT feature as part of the tablespace definition, which forces all extents in a tablespace to be a multiple of the minimum extent size; so even deliberate attempts to cause fragmentation problems by setting
inappropriate storage clauses on data segments would have little effect.

On a well managed system (and in the absence of LMTs)

    set pctincrease to 0 on the tablespace

    set a suitable minimum extent length of each tablespace

        (cp. the UNIFORM size of an LMT)

    set initial = next = minimum extent length

    disallow explicit storage clause on data segments

    work out ballpark figures for objects and allocate them     to the tablespace with the most appropriate minimum     extent length on the basis of a few (5 - 20 depending     on taste) extents per object.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Daniel A. Morgan wrote in message <3AFF7B57.874E8053_at_exesolutions.com>...


>

>If you are building tablespaces in 7.3 or 8.0 make the pct_increase on the

>tablespaces 1%. But be absolutely sure that you specify pct_increase of 0%

 in


>every single table and index create statement as Sybrand suggests. The

 first


>parameter will make sure that SMON coalesces freespace. The second will

 make


>sure that you don't fragment the tablespace. Under no conditions build a

 table


>or index without specifying a storage clause.

>

>Daniel A. Morgan

>