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)