SYSAUX tablespace is full with Autostats Advisor related objects

==========

Issue:
==========

SYSAUX tablespace is full with Autostats Advisor related objects

==========

Solution:

==========

Clean up old tasks

Check Current Status
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 UNLIMITED

Set limit for old tasks
BEGIN
  DBMS_ADVISOR.SET_TASK_PARAMETER(

     task_name=> 'AUTO_STATS_ADVISOR_TASK',                                parameter=> 'EXECUTION_DAYS_TO_EXPIRE', 
     value => 30);
END;
/

Check Status again

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

Check oldest task date

SELECT MIN(execution_start) 

FROM DBA_ADVISOR_EXECUTIONS WHERE task_name='AUTO_STATS_ADVISOR_TASK';

MIN(EXECUTION_STAR
------------------
27-AUG-19

Purge the expired tasks. 

This step might take time

BEGIN
  PRVT_ADVISOR.delete_expired_tasks;
END;
/

Check status in DBA_ADVISOR_EXECUTIONS 

SELECT task_id, task_name, execution_name, execution_start

  FROM DBA_ADVISOR_EXECUTIONS

 WHERE task_name='AUTO_STATS_ADVISOR_TASK'

 ORDER BY execution_start;