BC Clean sysaux - purge optimizer records

-- Doc. id 2660128.1

-- Check retention
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

-- Change to 3 days
EXEC DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (task_name => 'AUTO_STATS_ADVISOR_TASK', parameter => 'EXECUTION_DAYS_TO_EXPIRE', VALUE => 3);

-- Delete expired tasks
SELECT TASK_ID,TASK_NAME,EXECUTION_NAME,execution_start
FROM dba_advisor_executions
WHERE TASK_NAME='AUTO_STATS_ADVISOR_TASK';

EXEC prvt_advisor.delete_expired_tasks;

EXEC dbms_pdb.exec_as_oracle_script('alter table WRI$_ADV_OBJECTS move tablespace sysaux');

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