ORA-1654 : SYS.WRI$_ADV_OBJECTS_IDX_02

col segment_name format a30
col owner format a10
col tablespace_name format a10
col segment_type format a15
select segment_name,owner,tablespace_name,bytes/1024/1024 "SIZE(MB)",segment_type
from dba_segments
where tablespace_name='SYSAUX'
order by bytes desc;

• WRI$_ADV_OBJECTS
• WRI$_ADV_OBJECTS_IDX_01
• WRI$_ADV_OBJECTS_IDX_02
• WRI$_ADV_OBJECTS_PK
  

• AUTO_STATS_ADVISOR_TASK 用於自動統計顧問任務
• INDIVIDUAL_STATS_ADVISOR_TASK 用於手動統計顧問任務
  

SQL> col TASK_NAME format a25
SQL> col parameter_name format a35
SQL> col parameter_value format a20
SQL> set lines 120
SQL> 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

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

SQL> conn / as sysdba
SQL> exec prvt_advisor.delete_expired_tasks;

SQL> alter table WRI$_ADV_OBJECTS move;
SQL> alter index WRI$_ADV_OBJECTS_PK rebuild;
SQL> alter index WRI$_ADV_OBJECTS_IDX_01 rebuild;
SQL> alter index WRI$_ADV_OBJECTS_IDX_02 rebuild;

SQL> col task_name format a25
SQL> col EXECUTION_NAME format a15
SQL> select TASK_ID,TASK_NAME,EXECUTION_NAME ,execution_start from dba_advisor_executions where TASK_NAME='AUTO_STATS_ADVISOR_TASK';

select TASK_ID,TASK_NAME,EXECUTION_NAME ,execution_start from dba_advisor_executions where TASK_NAME='AUTO_STATS_ADVISOR_TASK';

select ADVISOR_NAME,TASK_NAME,LAST_EXECUTION,EXECUTION_END,STATUS from dba_advisor_tasks where task_name like 'AUTO%';

SQL> exec dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE');
PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_prefs('AUTO_STATS_ADVISOR_TASK') from dual;
DBMS_STATS.GET_PREFS('AUTO_STATS_ADVISOR_TASK')
--------------------------------------------------------------------------------
FALSE

• How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)
• SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 or Above Due To Statistics Advisor (Doc ID 2305512.1)