SYSAUX Tablespace Filling , Cause: WRI$_ADV_TASKS filled with AUTO__STATS_ADVISOR_TASK

The SYSAUX tablespace might fill up with records from advisor tasks. Use the following steps to identify and resolve the issue.

Step 1: Check SYSAUX Usage

SELECT OCCUPANT_NAME, SPACE_USAGE_KBYTES
FROM V$SYSAUX_OCCUPANTS
ORDER BY SPACE_USAGE_KBYTES DESC;

SELECT TASK_NAME, COUNT(*) CNT
FROM DBA_ADVISOR_OBJECTS
GROUP BY TASK_NAME
ORDER BY CNT DESC;

Step 2: Backup and Clean Up

If the record numbers are too large, directly deleting from WRI$_ADV_TASKS can impact the UNDO tablespace. Follow these steps:

1. Check rows inWRI$_ADV_OBJECTS not related to Auto Stats Advisor Task:

SELECT *
FROM WRI$_ADV_OBJECTS
WHERE TASK_ID != (SELECT DISTINCT ID FROM WRI$_ADV_TASKS WHERE NAME='AUTO_STATS_ADVISOR_TASK');

2. Create a backup table:

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

3. Truncate the table:

TRUNCATE TABLE WRI$_ADV_OBJECTS;

4. Reinsert rows:

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", "OTHER", "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", "OTHER", "SPARE_N1", "SPARE_N2", "SPARE_N3", "SPARE_N4",
"SPARE_C1", "SPARE_C2", "SPARE_C3", "SPARE_C4"
FROM WRI$_ADV_OBJECTS_NEW;

COMMIT;

5. Rebuild the indexes:

ALTER INDEX WRI$_ADV_OBJECTS_IDX_02 REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;

Optional Steps

1. Drop the task if needed:

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

2. Reinitialize for future needs:

EXEC DBMS_STATS.INIT_PACKAGE();

3. Adjust retention days:

EXEC DBMS_ADVISOR.SET_TASK_PARAMETER(
    task_name => 'AUTO_STATS_ADVISOR_TASK',
    parameter => 'EXECUTION_DAYS_TO_EXPIRE',
    value => 20);