tablespace coalesce

[learn]

When does smon automatically coaleseces in a tablespace?
Is it when pct increase is 0 or when pct_increase is non Zero

[julian]

The SMON process will wake up every 5th minute and checks for tablespaces with default pctincrease != 0

[roobaron]

If you're thinking on making your tablespace anything but pctincrease = 0 forget it, the overhead is not worth the savings. You get better results (in v8 and upwards) setting the minimum extent, making sure initial and next are the same and getting large tables (with larger initial/next extents) in their own tablespace.

Just write a scheduled script to run it once a day, every 6 hours, immediately before and after heavy DML.

Have Fun

[learn]

ROOBARON. Can you explain yopur point again? I did not get it.

[roobaron]

Setting PCTINCREASE on a tablespace to a non-zero value eg 1 will mean that SMON will wake up every 5 minutes to try a coalesce any tablespaces.
The overhead involved is non-trival (locking, excessive recursive calls)

To automatically coalesce tablespaces just write some SQL to do the job and schedule it to run once per day (at nite) or whatever period is correct for your application.

The other stuff, minimum extents and same extent sizes will reduce fragmentation due to every extent being able to be used by the tables within that tablespace.
This extra stuff was purely to point out that should be unnecessary to use SMON (via PCTINCREASE != 0) to coalesce fragmentation as it is cured thru being more proactive with the space management.

Hope this helps