oracle 10g SYSAUX表空間快速增長之WRI$_OPTSTAT_HISTGRM_HISTORY篇

在下午的檢查中，還發現另外幾個物件在sysaux表空間中佔據很大的空間：I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST，大小為4124M，WRI$_OPTSTAT_HISTGRM_HISTORY，大小為2893M，前者是後者的索引，此表是用來儲存歷史的的收集統計資訊的。

檢視metalink相關文章：

Statistics space used by SM/OPTSTAT in the SYSAUX tablespace is not reclaimed after purging [ID 454678.1]

Reduce SYSAUX Tablespace Occupancy Due to Fragmented TABLEs and INDEXes [ID 1271178.1]

發現metalink上認為的是這個表索引的碎片比較多，需要重整。在oracle10g中，重整表的方式有多種，為了不重新rebuild index，且不影響現在的業務，沒有采用move的方式，而是採用shrink 的方式對索引進行了收縮：

ALTER INDEX I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST SHRINK SPACE;

shrink後，此索引的大小縮至286.75M，同時此表的大小也有很明顯的縮小，現在只有309M，效果挺明顯；

再對WRI$_OPTSTAT_HISTGRM_HISTORY shrink:

ALTER TABLE WRI$_OPTSTAT_HISTGRM_HISTORY SHRINK SPACE;

報：ORA-10631: SHRINK clause should not be specified for this object

經過分析，原來此表上有函式索引，此索引正是上面的那個索引： I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST ，有函式索引的表是不能進行shrink操作的。

如果時間允許，可以嘗試按照move的方式對這些表重整一下，並重建索引。

-- To implement the solution, please execute the following steps::
1- Take a full backup of the database
2- Move the tables:

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;

3- For indexes, find the indexes for the above tables and rebuild them. In case an index is unusable, please see the following example:

SQL> select status from dba_indexes where index_name='I_WRI$_OPTSTAT_IND_OBJ#_ST';
SQL> select status from dba_indexes where index_name='I_WRI$_OPTSTAT_TAB_ST';

Assuming that indexes: I_WRI$_OPTSTAT_IND_OBJ#_ST & I_WRI$_OPTSTAT_TAB_ST are unusable, then, we have to do the following:

a.Determine the DDL's for the indexes using dbms_metadata package as shown in the example below
 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;
   
b.Then drop and recreate the indexes using the obtained DDL's.
   
c.Once done you can confirm the status by running the following query for example :
SQL> select status from dba_indexes where index_name='I_WRI$_OPTSTAT_IND_OBJ#_ST';
SQL> select status from dba_indexes where index_name='I_WRI$_OPTSTAT_TAB_ST';

4- sql> exec dbms_stats.alter_stats_history_retention(8); 
--&gt This will ensure that statistics history will be retained for at least 8 days.

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12129601/viewspace-714269/，如需轉載，請註明出處，否則將追究法律責任。