WRI$_ADV_OBJECTS占用过多SYSAUX空间的处理

近巡检发现有两个库的sysaux表空间使用率比其他要高，查询发现如下对象占了6G 多

select segment_name,segment_type,bytes/1024/1024 from dba_segments where tablespace_name='SYSAUX' order by 3;
...
SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024
------------------------------ ------------------------------------ ---------------
....
WRI$_ADV_OBJECTS TABLE 6123

表WRI$_ADV_OBJECTS存储统计信息顾问任务的数据
下面通过DBA_ADVISOR_PARAMETERS查询自动任务AUTO_STATS_ADVISOR_TASK信息
col TASK_NAME format a25
col parameter_name format a35
col parameter_value format a20
set lines 120
select TASK_NAME,parameter_name, parameter_value FROM DBA_ADVISOR_PARAMETERS WHERE task_name='AUTO_STATS_ADVISOR_TASK' and PARAMETER_NAME='EXECUTION_DAYS_TO_EXPIRE';
TASK_NAME PARAMETER_NAME PARAMETER_VALUE
------------------------- --------------------------------------------- -----------------------------------
AUTO_STATS_ADVISOR_TASK EXECUTION_DAYS_TO_EXPIRE 30
我们看到这里自动统计信息顾问的数据保留30天。

这里我们有几种方法解决sysaux空间问题
1. 空间扩容，直接想法。
2 减少数据的保留时间比如10天。
3 手工清理
具体才有哪个方式根据自己需求把，如果有大把的存储就扩容解决，简单。如果没有就手工清理 下，或者减少保留时间

下面是具体操作

1 扩容 :
alter tablespace sysaux add datafile '/oradata1/prod/sysaux02.dbf' size 30g;
2 减少保留时间
SQL> EXEC DBMS_ADVISOR.SET_TASK_PARAMETER(task_name=> 'AUTO_STATS_ADVISOR_TASK', parameter=> 'EXECUTION_DAYS_TO_EXPIRE', value => 10);

查询更改结果
SQL> select TASK_NAME,parameter_name, parameter_value FROM DBA_ADVISOR_PARAMETERS WHERE task_name='AUTO_STATS_ADVISOR_TASK' and PARAMETER_NAME='EXECUTION_DAYS_TO_EXPIRE';

TASK_NAME PARAMETER_NAME PARAMETER_VALUE
------------------------- ----------------------------------- --------------------
AUTO_STATS_ADVISOR_TASK EXECUTION_DAYS_TO_EXPIRE 10

3 代替窗口内的自动purge手工purge表

SQL> col task_name format a25
SQL> col EXECUTION_NAME format a15
SQL> select TASK_ID,TASK_NAME,EXECUTION_NAME ,execution_start from dba_advisor_executions where TASK_NAME='AUTO_STATS_ADVISOR_TASK';
TASK_ID TASK_NAME EXECUTION_NAME EXECUTION_START
---------- ------------------------- --------------- -------------------
6 AUTO_STATS_ADVISOR_TASK EXEC_11 2021-09-02 12:10:11
6 AUTO_STATS_ADVISOR_TASK EXEC_161 2021-09-14 22:31:00

SQL> conn / as sysdba
SQL> exec prvt_advisor.delete_expired_tasks;

PL/SQL procedure successfully completed.

SQL> col task_name format a25
SQL> col EXECUTION_NAME format a15
SQL> select TASK_ID,TASK_NAME,EXECUTION_NAME ,execution_start from dba_advisor_executions where TASK_NAME='AUTO_STATS_ADVISOR_TASK';
TASK_ID TASK_NAME EXECUTION_NAME EXECUTION_START
---------- ------------------------- --------------- -------------------
6 AUTO_STATS_ADVISOR_TASK EXEC_161 2021-09-14 22:31:00