SMON清理Temporary Extent现象

可以通过以下查询了解数据库中Temporary Extent的总数，在一定时间内比较其总数，若有所减少那么说明SMON正在清理Temporary segment
SELECT COUNT(*) FROM DBA_EXTENTS WHERE SEGMENT_TYPE='TEMPORARY';

也可以通过v$sysstat视图中的”SMON posted for dropping temp segment”事件统计信息来了解SMON收到清理要求的情况:
SQL> select name,value from v$sysstat where name like '%SMON%';

NAME VALUE
---------------------------------------------------------------- ----------
total number of times SMON posted 8
SMON posted for undo segment recovery 0
SMON posted for txn recovery for other instances 0
SMON posted for instance recovery 0
SMON posted for undo segment shrink 0
SMON posted for dropping temp segment 1

另外在清理过程中SMON会长期持有Space Transacton(ST)队列锁，其他会话可能因为得不到ST锁而等待超时出现ORA-01575错误:
01575, 00000, "timeout waiting for space management resource"
// *Cause: failed to acquire necessary resource to do space management.
// *Action: Retry the operation.

如何禁止SMON清理临时段

可以通过设置诊断事件event=’10061 trace name context forever, level 10′禁用SMON清理临时段(disable SMON from cleaning temp segments)。
alter system set events '10061 trace name context forever, level 10';本文来自cuug网。