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