sysaux 表空間不足問題處理

一現場，備份庫alter 日誌出現錯誤
ORA-1688: unable to extend table SYS.WRH$_SQLSTAT partition WRH$_SQLSTA_3344221469_3956 by 128 in                 tablespace SYSAUX
ORA-1688: unable to extend table SYS.WRH$_SQLSTAT partition WRH$_SQLSTA_3344221469_3956 by 128 in                 tablespace SYSAUX

根據錯誤提示是sysaux 表空間滿了

從10G 開始引入的,以前一些使用獨立表空間或系統表空間的資料庫元件現在在SYSAUX表空間中建立。透過分離這些元件和功能，SYSTEM表空間的負荷得以減輕.反覆建立一些相關物件及元件引起SYSTEM表空間的碎片問題得以避免。
如果SYSAUX表空間不可用,資料庫核心功能將保持有效;使用SYSAUX表空間的特點將會失敗或功能受限。可以從V$SYSAUX_OCCUPANTS view 檢視情況

V$SYSAUX_OCCUPANTS

V$SYSAUX_OCCUPANTS displays SYSAUX tablespace occupant information.
Column                                  Datatype              Description
OCCUPANT_NAME                 VARCHAR2(64)      Occupant name
OCCUPANT_DESC                  VARCHAR2(64)      Occupant description
SCHEMA_NAME                     VARCHAR2(64)      Schema name for the occupant
MOVE_PROCEDURE               VARCHAR2(64)      Name of the move procedure; null if not applicable
MOVE_PROCEDURE_DESC      VARCHAR2(64)      Description of the move procedure
SPACE_USAGE_KBYTES         NUMBER              Current space usage of the occupant (in KB)

檢視那些occupant空間佔用情況：

select OCCUPANT_NAME,SPACE_USAGE_KBYTES/1024/1024 from V$SYSAUX_OCCUPANTS;
SELECT s.object_name from dba_objects s where s.object_name like '%OPTSTAT%' and s.object_type='TABLE';

在Oracle10中表空間SYSAUX引入，oracle把統計資訊儲存在這裡，這也是為了更好的最佳化system表空間，
我們可以用檢視V$SYSAUX_OCCUPANTS 檢視，oracle有哪些資料存貯在SYSAUX中。

oracle的SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER的統計資訊都儲存在SYSAUX中
 
查詢當前SM/OPTSTAT的統計資訊的儲存時間
SQL> select dbms_stats.get_stats_history_retention from dual;
 
GET_STATS_HISTORY_RETENTION
---------------------------
                         31
 
修改SM/OPTSTAT的統計資訊的儲存時間為10天
SQL> exec dbms_stats.alter_stats_history_retention(10);
 
PL/SQL procedure successfully completed
 
SQL> select dbms_stats.get_stats_history_retention from dual;
 
GET_STATS_HISTORY_RETENTION
---------------------------
                         10
 

刪除20天前的統計資料
SQL> exec dbms_stats.purge_stats(sysdate-20);
 
PL/SQL procedure successfully completed
 
檢視當前有效的統計資料是到什麼時間的

SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
 
GET_STATS_HISTORY_AVAILABILITY
--------------------------------------------------------------------------------
12-2月 -12 07.15.49.000000000 下午 +08:00
 
再刪除7天前的統計資料
SQL> exec dbms_stats.purge_stats(sysdate-7);
 
PL/SQL procedure successfully completed
 
這個時候發現有效的統計資訊時間已經變了
SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
 
GET_STATS_HISTORY_AVAILABILITY
--------------------------------------------------------------------------------
14-2月 -12 07.15.57.000000000 下午 +08:00
 

沒有釋放空間是因為“purge_stats”用delete的方式刪除資料，雖然資料沒了，但是HWM還沒降下來，檢視OPTSTAT使用哪些表，然後降低其高水位即可。
注意：佔用空間的不一定都是object_name like '%OPTSTAT%'的物件，所以這個條件必要時可以去掉。
SQL> SELECT s.object_name from dba_objects s where s.object_name like '%OPTSTAT%' and s.object_type='TABLE';
 
OBJECT_NAME
--------------------------------------------------------------------------------
WRI$_OPTSTAT_TAB_HISTORY
WRI$_OPTSTAT_IND_HISTORY
WRI$_OPTSTAT_HISTHEAD_HISTORY
WRI$_OPTSTAT_HISTGRM_HISTORY
WRI$_OPTSTAT_AUX_HISTORY
WRI$_OPTSTAT_OPR
OPTSTAT_HIST_CONTROL$
 
7 rows selected
 
SQL>

再結合如下sql判斷哪個表大，然後就move哪個表
SQL> select a.table_name,a.num_rows from dba_tables a where  a.tablespace_name='SYSAUX' and a.table_name like '%OPTSTAT%';
 
TABLE_NAME                       NUM_ROWS
------------------------------ ----------
WRI$_OPTSTAT_OPR                      151
WRI$_OPTSTAT_AUX_HISTORY                0
WRI$_OPTSTAT_HISTGRM_HISTORY       139933
WRI$_OPTSTAT_HISTHEAD_HISTORY       14406
WRI$_OPTSTAT_IND_HISTORY             1196
WRI$_OPTSTAT_TAB_HISTORY             1323
 
6 rows selected
 
SQL>

再用如下語句查出相關表的索引，因為move表，索引會失效，需要重建索引
SQL> select i.index_name,i.table_name,i.status,i.table_owner
from dba_indexes i,dba_objects s where i.table_name=s.object_name and  s.object_name like '%OPTSTAT%' and s.object_type='TABLE';
 
INDEX_NAME                     TABLE_NAME                     STATUS   TABLE_OWNER
------------------------------ ------------------------------ -------- ------------------------------
I_WRI$_OPTSTAT_TAB_OBJ#_ST     WRI$_OPTSTAT_TAB_HISTORY       VALID    SYS
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
 
再次檢視錶空間的使用率，sysaux表空間確實減低不少。