--11.2.0.4中有bug 14084247 会导致awr信息不自动清理,导致sysaux表空间持续增长
现象:
--sysaux表空间使用过高,其中大部分item属于 SM/AWR
SELECT OCCUPANT_NAME "Item",
SPACE_USAGE_KBYTES / 1048576 "Space Used (GB)",
SCHEMA_NAME "Schema",
MOVE_PROCEDURE "Move Procedure"
FROM V$SYSAUX_OCCUPANTS
WHERE SPACE_USAGE_KBYTES > 1048576
ORDER BY "Space Used (GB)" DESC;
Item Space Used (GB) Schema Move Procedure
SM/AWR 4.84521484375 SYS
--查询历史snap信息,snap_id是从1开始,没有清理过:
SELECT count(*),snap_id FROM sys.WRH$_ACTIVE_SESSION_HISTORY GROUP BY snap_id ORDER BY snap_id
COUNT(*) SNAP_ID
2 1
10 2
12 3
2 4
2 5
1 6
2 7
4 8
8 9
3 10
4 11
2 12
--查询segment,WRH$_ACTIVE_SESSION_HISTORY只有两个分区