Oracle sysaux表空间异常增长的完美解决方法
时间：2022-06-20 10:25:14 | 栏目：Oracle | 点击：次
发现问题
某一业务系统数据库，sysaux表空间大小达到24GB+
查询sysaux表空间中对象的大小，超过1GB的列出，发现ASH相关的表过大。
SEGMENT_NAME OWNER SEGMENT_TYPE MB ------------------------------ -------------------- ------------------ ------ WRH$_ACTIVE_SESSION_HISTORY SYS TABLE PARTITION 7151 WRH$_EVENT_HISTOGRAM_PK SYS INDEX PARTITION 1856 WRH$_EVENT_HISTOGRAM SYS TABLE PARTITION 1777 WRH$_LATCH SYS TABLE PARTITION 1657 WRH$_SQLSTAT SYS TABLE PARTITION 1344
查看快照在数据库中的保留时长
可以看到，AWR设定的保留时间为8天，WRH$_ACTIVE_SESSION_HISTORY确保留了2140天的数据，需清理。
查看AWR快照未清理原因
WRH$_ACTIVE_SESSION_HISTORY由于分区失败，导致所有数据均在同一个表空间，所以删除过期快照失败。
问题原因分析：
Oracle根据保留策略决定需要清除哪些行。在大型AWR表的情况下使用了一种特殊的机制，我们将快照数据存储在分区中。从这些表中清除数据的一种方法是删除只包含超过保留条件的行的分区。在夜间清除任务中，只有当分区中的所有数据都已过期时，我们才会删除该分区。如果该分区至少包含一行，根据保留策略，该行不应被删除，那么该分区将不会被删除，因此该表将包含旧数据。
如果没有发生分区拆分（无论出于何种原因），那么我们最终可能会遇到这样的情况：我们必须等待最新的条目过期，然后才能删除它们所在的分区。这可能意味着，一些较旧的条目可以在有效期过后显著保留。其结果是数据未按预期清除。
处理方法：
根据MOS：WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy (Doc ID 387914.1)处理步骤
1.Check the partition details for the offending table before the split:
SELECT owner, segment_name, partition_name, segment_type, bytes/1024/1024/1024 Size_GB FROM dba_segments WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY';
2.Split the partitions so that there is more chance of the smaller partition being purged:
alter session set "_swrf_test_action" = 72;
NOTE: This command will split partitions for ALL partitioned AWR objects. It also initiates a single split; it does not need to be disabled and will need to be repeated if multiple splits are required.
3.Check the partition details for the offending table after the split:
SELECT owner, segment_name, partition_name, segment_type, bytes/1024/1024/1024 Size_GB FROM dba_segments WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY';
以上步骤执行完成后，再truncate原来的分区，只保留新分区，释放空间
ALTER TABLE WRH$_ACTIVE_SESSION_HISTORY TRUNCATE PARTITION WRH$_ACTIVE_xxxxx;
检查索引是否正常
select index_name,partition_name,status from dba_ind_partitions where index_name='WRH$_ACTIVE_SESSION_HISTORY_PK';