Re: Setting pctincrease 0 at the tablespace level

comments below
"Dino Hsu" <dino1_at_ms1.hinet.net> wrote in message news:ia02ht0iku7utfb2t6fkocr0geecrjkkig_at_4ax.com...
> Dear all,
>
> In a book, it says:
> "Note: Setting pctincrease to 0 at the tablespace level affects
> ORACLE's ability to automatically coalesce free space in the
> tablespace. Set the default pctincrease for the tablespace to a very
> low value, such as 1."
>
> My questions:
> 1.Why the free-space-coalescing problem?

because SMON the background process that coalesces free space, doesn't when PCTINCREASE is set to zero.

> 2.Do you think pctincrease=1 is good?

well it is better than the default of 50, but see below.

> 3.Do you think fixed extent size stand well most of the time?

<opinion> This should be the default situation for all of your databases. Anyone who wishes to change this policy for a particular situation should have a well argued reason as to why. If you rigorously stick to a fixed extent size per tablespace then you will NOT have fragmentation problems. All objects in the tablespace use the same extent size, so there will never be a situation where there is enough free space available, but in smaller contiguous chunks than the size of the next extent. </opinion>

It might be worth (re)reading the extended discussion about locally managed tablespaces between Nuno and Howard, amongst others from about a month ago on this group, this effectively outlines the considerations in some depth. (you can use locally managed tablespaces as a method of forcing uniform extent size).

HTH (and is right this time)

--

Niall Litchfield
Oracle DBA
Audit Commission UK