RE: Coalescing Freespace / pctincrease = 1

It is my understanding that in version 7.0 and later, the SMON background process automatically coalesces neighboring free extents if the default PCTINCREASE setting for the TABLESPACE is NON-ZERO.

If the default setting for the TABLESPACE is ZERO, then the free space in the tablespace will not be coalesced automatically by the database (SMON). You will have to do this manually.

Therefore in order for PMON to be able to AUTOMATICALLY colesce a tablespace the PCTFREE for the TABLESPACE must be > 0. Oracle recommends a default value of 1.

NOTE1 : SMON looks at the PCTINCREASE that is set in the TABLESPACE storage parm NOT the TABLE storage parm.
Therefore if you allocate your TABLEs by specifying a PCTINCREASE = 0 (ie; do not leave blank or you will pickup the TABLESPACE's default storage parm) in the storage parm then your table's segments will grow by the value specifed by the NEXT parm.
In other words the PCTINCREASE value in the TABLESPACE will have no effect on the segements in the TABLE (unless you let them use the Tablespace default parms).

NOTE2 : RBS and TEMP tablespace should not be colesced. You do not want to colesce TEMP because you want to be able to reuse the allocated extents while the database is up. If you free up these extents then the database would need to dynamically (lot of overhead associated with this) re-allocate the extents when it needs them.
It is better to leave the extents pre-allocated for the next guy to use. As for the RBS - you should let the database manage the rollback segment extents. Make sure you have the OPTIMAL setting tuned properly - and it will reclaim the unused extents for you. Therefore PCT_INCREASE for TEMP and RBS should be =0. All other tablespaces should be =1. Received on Thu Dec 07 2000 - 12:34:10 CST