背景

??經歷了一次“Oracle SYSAUX表空間異常爆滿”，當時查資料幾種解決思路，在此做個匯總方便后續查看。不嚴謹的地方歡迎大家指正

問題介紹

• sysaux表空間中占用空間的組件

set line 800
col OCCUPANT_NAME for a30
col OCCUPANT_DESC for a60
select OCCUPANT_NAME,OCCUPANT_DESC,SPACE_USAGE_KBYTES/1024 USAGE_MB
from V$SYSAUX_OCCUPANTS order by SPACE_USAGE_KBYTES desc;
OCCUPANT_NAME                  OCCUPANT_DESC                                                  USAGE_MB
------------------------------ ------------------------------------------------------------ ----------
SM/OPTSTAT -->優化器統計信息   Server Manageability - Optimizer Statistics History            23922.25

• SYSAUX 表空間

表空間名                       表空間大小(M) 已使用空間(M) 使用比   空閑空間(M)  最大塊(M)
------------------------------ ------------- ------------- -------- ----------- ----------
SYSAUX                                 32720      32673.31   99.86%       46.69    

由此可以看到SM/OPTSTAT組件占比 SYSAUX 空間：73%

SM/OPTSTAT簡介

SM/OPTSTAT是用于存儲老的統計信息。
（1）10G之前，當對表/字段/索引做了相應的統計信息之后，新的統計信息就會覆蓋老的統計信息，也就是說的無法直接找回統計信息，要找回，只能事先通過dbms_stats導出來。
（2）10G之后，就不必了，它會自動的存到相應的表里，而這些表是存在sysaux的，但這也引出了一個問題：如果這些表的數據不斷的增長，而不把老的數據刪除的話，sysaux遲早會被撐爆。
默認的情況下,系統會為SM/OPTSTAT保留31天的記錄，可以通過dbms_stats.get_stats_history_retention 來確定。

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                         31

SM/OPTSTAT保留的時間可以通過dbms_stats.alter_stats_history_retention來控制。

-- 將歷史統計信息保留時間設為無限:
SQL> exec dbms_stats.alter_stats_history_retention(-1);
-- 將歷史統計信息保留時間設為7天
SQL> exec dbms_stats.alter_stats_history_retention(7);
-- 查看歷史統計信息保留時間
SQL> select dbms_stats.get_stats_history_availability from dual;
GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
04-NOV-22 10.00.07.354209000 PM +08:00

如果SM/OPTSTAT確實占用了比較多的空間，要刪除某個時間前的記錄可以用：dbms_stats.purge_stats

-- 清理3天前的歷史統計信息
SQL>  exec dbms_stats.purge_stats(sysdate-3);
PL/SQL procedure successfully completed.

這個SP其實只從存儲歷史統計信息的表里刪除記錄，這樣的話，就會出現一種情況，刪除了大量的數據，但這些表占用的空間并沒有釋放,也就是HWM不會降下來的。這時要手工處理

處理方案

方案一：來自(文檔 ID 1055547.1)的方案：

• 涉及的表
  
• 原因
  
• 解決
  
  

方案二：直接truncate 表

• 1、將歷史統計信息保留時間設為無限:

exec dbms_stats.alter_stats_history_retention(-1);

• 2、truncate TABLE:

truncate table sys.WRI$_OPTSTAT_HISTHEAD_HISTORY; 
truncate table sys.WRI$_OPTSTAT_HISTGRM_HISTORY;

• 3、清理歷史統計信息(清理7天前的信息)

exec dbms_stats.purge_stats(sysdate-7); 

• 4、將歷史統計信息保留時間設為7天（默認31天）

exec dbms_stats.alter_stats_history_retention(7);

• 5、避免有其它問題，執行一次數據庫的收集任務：

SQL> exec dbms_stats.gather_database_stats_job_proc();
PL/SQL procedure successfully completed.

方案三：move table 達到清理碎片的目地

• 將表move 到users表空間

SQL> alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move tablespace users;
-- LOB 字段字段需要單獨move 
SQL> alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move tablespace users LOB (expression) STORE AS lobsegment (TABLESPACE users);
SQL> alter index I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST  rebuild online;
SQL> alter index I_WRI$_OPTSTAT_HH_ST rebuild online;

• 重新將表move 回原空間：SYSAUX

SQL> alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move tablespace SYSAUX;
SQL> alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move tablespace SYSAUX LOB (expression) STORE AS lobsegment (TABLESPACE SYSAUX);
SQL> alter index I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST  rebuild online;
SQL> alter index I_WRI$_OPTSTAT_HH_ST rebuild online;

方案四：重創建表

• 根據create table as 創建一張指定范圍內的表備做備份，truncate 表后再重新導入

SQL> CREATE TABLE WRI$_OPTSTAT_HISTGRM_HISTORY_B AS SELECT * FROM WRI$_OPTSTAT_HISTGRM_HISTORY where to_char(savtime,'yyyy-mm-dd')>'2022-11-09';
SQL> TRUNCATE TABLE  WRI$_OPTSTAT_HISTGRM_HISTORY ;
SQL> INSERT INTO WRI$_OPTSTAT_HISTGRM_HISTORY  SELECT * FROM  WRI$_OPTSTAT_HISTGRM_HISTORY_B;
SQL> COMMIT;
SQL> drop table WRI$_OPTSTAT_HISTGRM_HISTORY_B purge;

總結：

• 不一定是全的，但應該有一款適合你！