oracle 临时段详解,对SMON和临时段回收的一些疑问

Cleaning Up Stray Temporary Segments

Some temporary segments are not cleaned up as expected, and can often persist for hours. This sometimes results in tablespaces running out of space inappropriately. To avoid such problems, DBAs can trigger the cleanup of straggling temporary segments.

What causes stray temporary segments?

When a segment is dropped, its extents are not freed immediately. Initially the process dropping the segment just changes the segment's type to a temporary segment. This operation can be rolled back if the statement fails for any reason. The temporary segment is normally cleaned up and its extents freed upon the conclusion of the call. However, if the data dictionary cache row representing the segment is still dirty or in use, temporary segment cleanup cannot occur at this time. This applies in particular to temporary segments released by recursive calls. Because the parent transaction has not yet committed, such temporary segments cannot be cleaned up immediately.

How do stray temporary segments get cleaned up?

The task of cleaning up straggling temporary segments and freeing their extents falls to the SMON process. Although SMON wakes up every five minutes, unless it is explicitly posted by another process, it only checks for temporary segments to clean up once every two hours and five minutes. Even then it will only clean up five temporary segments at most, and only if it can get the required locks within 5 seconds. So temporary segment cleanup can appear to take a long time, even days.

However, SMON also performs temporary segment cleanup when it is posted explicitly by another process. You can make use of this fact to force SMON to clean up temporary segments more promptly. SMON is posted whenever a space transaction fails. So you can trigger temporary segment clean up by attempting to create a table of 2 extents with a small INITIAL and an impossible NEXT extent size. However, a more elegant way of posting SMON is to use the ORADEBUG WAKEUP command from within SQL*Plus (or SVRMGRL before 8.1). There is an APT script that does this, called post_smon.sql.