Thursday, 26 March 2009

How does one coalesce free space?

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 command:

ALTER TABLESPACE ... COALESCE;

For releases older than 7.3, use:

SQL> alter session set events 'immediate trace name coalesce level n';

where 'n' is the tablespace number you get from 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.

No comments:

Post a Comment