oracle 19c 处理 sysaux因AUTO_STATS_ADVISOR_TASK导致空间暴增的问题

郑全

通过 $ORACLE_HOME/rdbms/admin/utlsyxsz.sql 脚本，

如果确定最高空间占用是：

SM/ADVISOR

那就可以使用下面办法处理：

1.创建新表并保留非 AUTO_STATS_ADVISOR_TASK数据

SQL> CREATE TABLE WRI$_ADV_OBJECTS_NEW AS SELECT * FROM WRI$_ADV_OBJECTS WHERE TASK_ID !=
(SELECT DISTINCT ID FROM WRI$_ADV_TASKS WHERE NAME='AUTO_STATS_ADVISOR_TASK');

2. 截断原表
SQL> TRUNCATE TABLE WRI$_ADV_OBJECTS;

3.把备份表中数据插回原表 ###
SQL>

INSERT INTO WRI$_ADV_OBJECTS

("ID" ,"TYPE" ,"TASK_ID" ,"EXEC_NAME" ,"ATTR1" ,"ATTR2","ATTR3" ,"ATTR4" ,"ATTR5" ,"ATTR6" ,"ATTR7" ,"ATTR8" ,"ATTR9"

,"ATTR10","ATTR11","ATTR12","ATTR13","ATTR14","ATTR15","ATTR16","ATTR17","ATTR18","ATTR19","ATTR20","SPARE_N1" ,

"SPARE_N2" ,"SPARE_N3" ,"SPARE_N4" ,"SPARE_C1" ,"SPARE_C2" ,"SPARE_C3","SPARE_C4" )

SELECT "ID" ,"TYPE" ,"TASK_ID" ,"EXEC_NAME" ,"ATTR1" ,"ATTR2" ,"ATTR3"     ,"ATTR4" ,"ATTR5" ,"ATTR6" ,"ATTR7" ,"ATTR8" ,"ATTR9" ,

"ATTR10","ATTR11","ATTR12","ATTR13","ATTR14","ATTR15","ATTR16","ATTR17","ATTR18","ATTR19","ATTR20","SPARE_N1" , "SPARE_N2" ,"SPARE_N3" ,"SPARE_N4" ,"SPARE_C1" ,"SPARE_C2" ,"SPARE_C3","SPARE_C4" FROM WRI$_ADV_OBJECTS_NEW;

SQL> COMMIT;

4. 重建索引
SQL> ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;
SQL> ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;

5.从数据字典删除统计信息任务

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