Oracle SYSAUX表空间使用率过高进行清理

巡检的时候发现SYSAUX表空间使用率超过了80%，将近达到了60G:

TABLESPACE_NAME      Allocated (MB)  Free (MB)  Used (MB) PERCENTFREE
-------------------- -------------- ---------- ---------- -----------
SYSAUX                        60440       7907      52532          14

set linesize 200
col owner for a15
col segment_name for a30
col segment_type for a20
col partition_name for a20
select owner,segment_name,segment_type,partition_name,bytes/1024/1024/1024 size_gb from dba_segments where tablespace_name='SYSAUX' and bytes/1024/1024/1024 > '1';

OWNER           SEGMENT_NAME                   SEGMENT_TYPE         PARTITION_NAME          SIZE_GB
--------------- ------------------------------ -------------------- -------------------- ----------
SYS             WRI$_ADV_OBJECTS               TABLE                                     20.9179688
SYS             WRI$_ADV_OBJECTS_PK            INDEX                                     7.24707031
SYS             WRI$_ADV_OBJECTS_IDX_01        INDEX                                     10.9150391
SYS             WRI$_ADV_OBJECTS_IDX_02        INDEX                                     9.17773438
SYS             HEATMAP                        SYSTEM STATISTICS                         1.27246094

最大的表：WRI$_ADV_OBJECTS

清理WRI$_ADV_OBJECTS相关数据

--表越大，索引越大的执行时间越长，耐心等待

DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/
 
EXEC DBMS_STATS.INIT_PACKAGE();

ALTER TABLE WRI$_ADV_OBJECTS MOVE;
ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;

--禁用Optimizer Statistics Advisor Task
DECLARE
filter1 CLOB;
BEGIN
filter1 := DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER('AUTO_STATS_ADVISOR_TASK','EXECUTE',NULL,'DISABLE');
END;
/

--查看保留时间

col task_name for a30
col parameter_name for a30
col parameter_value for a10
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';

 
 
--修改保留时间 10天
BEGIN
 DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(task_name => 'AUTO_STATS_ADVISOR_TASK',                                         parameter => 'EXECUTION_DAYS_TO_EXPIRE',                                         value     => 10);
END;
/