I_WRI$_OPTSTAT_TAB_ST WRI$_OPTSTAT_TAB_HISTORY VALID SYS
I_WRI$_OPTSTAT_IND_OBJ#_ST WRI$_OPTSTAT_IND_HISTORY VALID SYS
I_WRI$_OPTSTAT_IND_ST WRI$_OPTSTAT_IND_HISTORY VALID SYS
I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST WRI$_OPTSTAT_HISTHEAD_HISTORY VALID SYS
I_WRI$_OPTSTAT_HH_ST WRI$_OPTSTAT_HISTHEAD_HISTORY VALID SYS
I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST WRI$_OPTSTAT_HISTGRM_HISTORY VALID SYS
I_WRI$_OPTSTAT_H_ST WRI$_OPTSTAT_HISTGRM_HISTORY VALID SYS
I_WRI$_OPTSTAT_AUX_ST WRI$_OPTSTAT_AUX_HISTORY VALID SYS
I_WRI$_OPTSTAT_OPR_STIME WRI$_OPTSTAT_OPR VALID SYS
10 rows selected
SQL>
降低HWM
sql> alter table WRI$_OPTSTAT_TAB_HISTORY move;
sql> alter table WRI$_OPTSTAT_OPR move;
sql> alter table WRI$_OPTSTAT_IND_HISTORY move;
sql> alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move;
sql> alter table WRI$_OPTSTAT_HISTGRM_HISTORY move;
sql> alter table WRI$_OPTSTAT_AUX_HISTORY move;
sql> alter table OPTSTAT_HIST_CONTROL$ move;
重建索引
alter index I_WRI$_OPTSTAT_TAB_OBJ#_ST rebuild online;
alter index I_WRI$_OPTSTAT_TAB_ST rebuild online;
alter index I_WRI$_OPTSTAT_IND_OBJ#_ST rebuild online;
alter index I_WRI$_OPTSTAT_IND_ST rebuild online;
alter index I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST rebuild online;
alter index I_WRI$_OPTSTAT_HH_ST rebuild online;
alter index I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild online;
alter index I_WRI$_OPTSTAT_H_ST rebuild online;
alter index I_WRI$_OPTSTAT_AUX_ST rebuild online;
如果索引编译不成功，就要create indexe
用如下语句生成DDL语句
SQL> set long 4000
SQL> select dbms_metadata.get_ddl('INDEX','I_WRI$_OPTSTAT_IND_OBJ#_ST','SYS') from dual;
SQL> select dbms_metadata.get_ddl('INDEX','I_WRI$_OPTSTAT_TAB_ST','SYS') from dual;
如何恢复统计信息
用如下语句查到统计信息的时间点
select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history；
可以按需要根据时间点恢复统计信息
execute DBMS_STATS.RESTORE_TABLE_STATS ('owner','table',date)
execute DBMS_STATS.RESTORE_DATABASE_STATS(date)
execute DBMS_STATS.RESTORE_DICTIONARY_STATS(date)
execute DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS(date)
execute DBMS_STATS.RESTORE_SCHEMA_STATS('owner',date)
execute DBMS_STATS.RESTORE_SYSTEM_STATS(date)
例如：
SQL> execute dbms_stats.restore_table_stats ('SKATE','BK_ADMIN',sysdate -1);
PL/SQL procedure successfully completed
SQL>
参考文档：[ID 329984.1], [ID 452011.1],[ID 454678.1]
---------end--------