SYSAUX - AUTO_STATS_ADVISOR_TASK expired tasks not being purged
We run a single instance Oracle 19.28. non PDB
We try following the steps from this article since our SYSAUX space is getting huge and find the most space usage occupant is SM/ADVISOR:
It looks like running:
sql> exec prvt_advisor.delete_expired_tasks;
has no effect and does not purge expired stats even if we adjust the EXECUTION_DAYS_TO_EXPIRE:
SQL> EXEC DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (task_name => 'AUTO_STATS_ADVISOR_TASK', parameter => 'EXECUTION_DAYS_TO_EXPIRE', value => 10);
As of today 2025-08-22 there are still tasks going back to April which is more than 10 or even 30 days ago!