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 資料表空間滿了
 
 Oracle從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
 
 

刪除16天前的統計資料
 SQL> exec dbms_stats.purge_stats(sysdate-20);
 
 PL/SQL procedure successfully completed
 
 SQL>

查看當前有效統計資料是到什麼時間的
 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

1 2 下一頁