oracle 删除awr,AWR不自动删除导致SYSAUX表空间满

不知何时何原因，一个数据库的AWR不自动删除了，然后慢慢的SYSAUX表空间满了，alert日志也开始报警

其主要是WRH$_ACTIVE_SESSION_HISTORY 的一个分区表占了很多，没遇到过的问题，总是感觉很紧张，尤其又是生产库

于是乎，想了个笨方法开始手动删WRH$_ACTIVE_SESSION_HISTORY的数据，删的差不多了就开始收缩表，收缩完表空间有地了可是过段时间又满了，心想这笨方法可不行，开始找方法

在官方发现文档 387914.1就是说的这个问题

CAUSE

Oracle

decides what rows need to be purged based on the retention policy. There is a

special mechanism which is used in the case of the large AWR tables where we

store the snapshot data in partitions. One method of purging data from these

tables is by removing partitions that only contain rows that have exceeded the

retention criteria. During the nightly purge task, we only drop the partition

if all the data in the partition has expired. If the partition contains at

least one row which, according to the retention policy shouldn't be

removed,  then  the partition won't be dropped and as such the table

will contain old data.

If

partition splits do not occur (for whatever reason), then we can end up with a

situation where we have to wait for the latest entries to expire before the

partition that they sit in can be removed. This can mean that some of the older

entries can be retained significantly past their expiry date. The result of

this is that the data is not purged as expected.

SOLUTION

A

potential solution to this issue is to manually split the partitions of the

partitioned AWR objects such that there is more chance of the split partition

being purged.You will still have to wait for all the rows in the new partitions

to reach their retention time but with split partitions there is more chance of

this happening. you can manually split the partitions using the following

undocumented command:

alter session set "_swrf_test_action" =

72;

To

perform a single split of all the AWR partitions.

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';

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.

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';

其方法就是执行alter session set "_swrf_test_action" = 72; 让AWR相关的分区表都创建一个新的分区，等待老分区的数据过期，oracle就会自动删除。

可眼下表空间已经满了，等不到老分区的数据过期，可咋办

那么可以先修改AWR的有效保留期，比如改成5天。等5天后oracle删掉分区在把保留期改回来。如果老数据需要保留可以导出AWR的信息。

但是也有个问题，这次的数据已经删了，那么以后oracle会自动删吗？不会每次都要手动执行一下这个命了吧？希望有经验的朋友告诉下，或者让时间证明