- SMON coalesces free space (extents) into larger, contiguous extents every 2 hours and even then, only for a short period of time.
- SMON will not coalesce free space if a tablespace's default storage parameter "pctincrease" is set to 0.
- With Oracle 7.3 one can manually coalesce a tablespace using the ALTER TABLESPACE tblspace_temp COALESCE; command, until then use
SQL> alter session set events 'immediate trace name coalesce level n'
Where 'n' is the tablespace number.
SELECT TS#, NAME FROM SYS.TS$;- You can get status information about this process by selecting from the SYS.DBA_FREE_SPACE_COALESCED dictionary view.
