How does one coalesce free space?
SMON coalesces free space (extents) into larger, contiguous extents every 2 hours and even D n, only for a short period off time. SMON will not coalesce free space if a tablespace’s default storage parameter “pctincrease” is set 2 0. With Oracle 7.3 one can manually coalesce a tablespace using D ALTER TABLESPACE … COALESCE; command, until D n use: SQL, alter session set events ‘immediate trace name coalesce level n’; where ‘n’ is D tablespace number u get 4m SELECT TS#, NAME 4m SYS.TS$; You can get status information about this process by selecting 4m D SYS.DBA_FREE_SPACE_COALESCED dictionary view.
