SYSTEM和SYSAUX表空間存儲的內容有哪些區別？若SYSAUX表空間佔用過大則應該如何處理？
- 2019 年 10 月 10 日
- 筆記
今天小麥苗給大家分享的是SYSTEM和SYSAUX表空間存儲的內容有哪些區別？若SYSAUX表空間佔用過大則應該如何處理？。
SYSTEM和SYSAUX表空間存儲的內容有哪些區別？若SYSAUX表空間佔用過大則應該如何處理？
在一般情況下，企業產生的業務數據應該存放在單獨的數據表空間，而不應該使用系統已存在的表空間，尤其不能將業務數據保存到SYSTEM和SYSAUX表空間中，所以，DBA需要著重關注SYSTEM和SYSAUX表空間的佔用情況。
Oracle伺服器使用SYSTEM表空間管理整個資料庫。這個表空間包含系統的數據字典和關於資料庫的管理資訊，這些資訊均包含在SYS方案中，只有SYS用戶或者擁有所需許可權的其它管理用戶才可訪問這些資訊。SYSTEM表空間用於核心功能（例如數據字典表）。
SYSAUX是SYSTEM表空間的輔助表空間。Oracle DB早期版本中某些使用SYSTEM表空間或其本身表空間的組件和產品現在改為使用SYSAUX表空間。每個Oracle Database 10g（或更高版本）資料庫都必須擁有SYSAUX表空間。輔助表空間SYSAUX用於附加的資料庫組件，例如，OEM庫（Oracle Enterprise Manager Repository）、AWR快照資訊庫、統計資訊、審計資訊等。
SYSTEM和SYSAUX表空間是在創建資料庫時創建的必需存在的表空間。這些表空間必須聯機。在OPEN狀態下，SYSAUX表空間可以離線以執行表空間恢復，而SYSTEM表空間則不能，這兩種表空間都不能設置為只讀狀態。在MOUNT狀態下，任何錶空間都可以離線。
SYSTEM表空間的大小一般變化不大，而SYSAUX表空間在默認條件下如果不做任何配置，那麼隨著時間的推移，會越來越大。所以，如果SYSAUX表空間過大，那麼應該及時診斷清理該表空間。
對於SYSTEM表空間而言，如果佔用過大，那麼一般情況下是由於審計表（SYS.AUD$）過大引起的。需要將審計表移動到其它表空間中，然後再清理審計表（TRUNCATE TABLE SYS.AUD$）即可。需要注意的是，如果審計表過大，那麼應該分部去清理審計表，詳細步驟可以參考審計部分。
對於SYSAUX表空間而言，如果佔用過大，那麼一般情況下是由於AWR資訊或對象統計資訊沒有及時清理引起的，具體原因可以通過如下的SQL語句查詢：
SELECT OCCUPANT_NAME "Item",
SPACE_USAGE_KBYTES / 1048576 "Space Used (GB)",
SCHEMA_NAME "Schema",
MOVE_PROCEDURE "Move Procedure"
FROMV$SYSAUX_OCCUPANTS
WHERESPACE_USAGE_KBYTES > 1048576
ORDER BY"Space Used (GB)" DESC;
如果OCCUPANT_NAME列為SM/AWR（Server Manageability – Automatic Workload Repository），那麼表示AWR資訊佔用過大；如果該列為SM/OPTSTAT（ServerManageability – Optimizer Statistics History），那麼表示優化器統計資訊佔用過大。
也可以直接查詢DBA_SEGMENTS視圖獲取資訊：
SELECT D.SEGMENT_NAME, D.SEGMENT_TYPE,SUM(BYTES)/1024/1024 SIZE_M
FROMDBA_SEGMENTS D
WHERED.TABLESPACE_NAME = 'SYSAUX'
GROUP BYD.SEGMENT_NAME, D.SEGMENT_TYPE
ORDER BYSIZE_M DESC;
然後查詢佔用空間較大的表，即可得到佔用空間較大的原因，下面分別討論。
（一）AWR資訊佔用過大
如果確認是AWR資訊佔用空間過大，那麼還可以使用如下的SQL腳本獲取AWR佔用資訊的詳細資訊：
sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/awrinfo.sql
如果AWR資訊佔用過大，那麼可以通過設置AWR的保留時間來減小AWR資訊的存儲空間。通過如下的SQL語句可以獲取AWR的保留時間：
SELECT * FROM DBA_HIST_WR_CONTROL;
通過如下的SQL語句可以設置AWR資訊的保留時間為7天（7*24*60），每隔1小時收集一次AWR資訊：
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL=>60,RETENTION=>7*24*60);
需要注意的是，在Oracle 10g中，AWR默認保留7天，在Oracle 11g中，AWR默認保留8天。
在以上設置完成後，可以刪除不需要的AWR快照資訊，從而釋放SYSAUX表空間，相關SQL語句如下所示：
SELECT MIN(SNAP_ID),MAX(SNAP_ID) FROMDBA_HIST_SNAPSHOT;
SELECT MIN(SNAP_ID),MAX(SNAP_ID) FROMDBA_HIST_ACTIVE_SESS_HISTORY;
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
LOW_SNAP_ID => 1,
HIGH_SNAP_ID => 36768,
DBID=> 1148453265);
END;
如果DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE執行太慢，那麼可以先執行TRUNCATE操作：
select distinct 'truncate table '||segment_name||';',s.bytes/1024/1024
fromdba_segments s
wheres.segment_name like 'WRH$%'
andsegment_type in ('TABLE PARTITION', 'TABLE')
ands.bytes/1024/1024>100
order bys.bytes/1024/1024/1024 desc;
執行完TRUNCATE操作後，再執行DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE。需要注意的是，以上TRUNCATE操作會將AWR中的所有資訊全部清除。所以，需要先確認釋放需要這些AWR資訊，當然也可以先把需要的AWR資訊做導出操作，然後再清空以上AWR資訊。
需要注意的是，DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE是通過DELETE操作來完全清理工作的。所以，執行完成後，並不會真正的釋放SYSAUX表空間。此時，應該對相關的表執行MOVE或TRUNCATE操作。在執行MOVE操作時，由於AWR資訊的表都是分區表，不能對分區表全表執行MOVE操作，所以需要單獨對分區執行MOVE操作，例如：
ALTER TABLE WRH$_ACTIVE_SESSION_HISTORY MOVEPARTITION 分區名稱;
執行完MOVE操作後，需要對索引進行重建。同理，對於分區索引，只能對分區的單個索引進行重建，而不能總體重建：
ALTER INDEX WRH$_ACTIVE_SESSION_HISTORY_PK REBUILDPARTITION 分區名稱;
需要注意的是，可以在以上SQL後加上「UPDATE GLOBALINDEXES」子句讓全局索引不失效。
（二）統計資訊佔用過大
如果統計資訊佔用空間過大，那麼可以修改統計資訊的保留時間。統計資訊默認保留31天，過期的統計資訊會自動被刪除。
SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROMDUAL; –查詢統計資訊的保留時間
EXEC DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(7); –設置統計資訊的保留時間
若發現統計資訊佔用了SYSAUX上的大量空間，則可以考慮使用DBMS_STATS.PURGE_STATS過程實施清理。
以下的SQL語句對於診斷SYSAUX表空間的佔用情況非常有用：
SELECT DBMS_STATS.GET_STATS_HISTORY_AVAILABILITYFROM DUAL;
SELECT MIN(SAVTIME), MAX(SAVTIME) FROM WRI$_OPTSTAT_TAB_HISTORY;
SELECT MIN(SAVTIME), MAX(SAVTIME) FROMSYS.WRI$_OPTSTAT_IND_HISTORY;
SELECT MIN(SAVTIME), MAX(SAVTIME) FROMSYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;
SELECT MIN(SAVTIME), MAX(SAVTIME) FROMSYS.WRI$_OPTSTAT_HISTGRM_HISTORY;
SELECT MIN(SAVTIME), MAX(SAVTIME) FROMSYS.WRI$_OPTSTAT_AUX_HISTORY;
SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_TAB_HISTORY;
SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_IND_HISTORY;
SELECT COUNT(*) FROMSYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;
SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;
SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_AUX_HISTORY;
SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_OPR;
以下SQL可以查詢到無效的ASH資訊：
SELECT COUNT(*)
FROMSYS.WRH$_ACTIVE_SESSION_HISTORY A
WHERE NOTEXISTS (SELECT 1
FROM SYS.WRM$_SNAPSHOT B
WHERE A.SNAP_ID = B.SNAP_ID
AND A.DBID = B.DBID
AND A.INSTANCE_NUMBER = B.INSTANCE_NUMBER);
最後需要說明的一點是，負責收集和清理AWR資訊的後台進程為MMON，而隱含參數「_swrf_test_action」可以調試MMON的行為，可以和10046事件結合使用。MMON進程每分鐘都會自動刷新一定的AWR數據到磁碟上，默認情況下，MMON每30分鐘做一次AWR資訊的清理工作。在trace文件中可以看到「MMON Auto-Purge cycle」字樣。
& 說明：
有關SYSTEM和SYSAUX的更多內容可以參考我的BLOG：http://blog.itpub.net/26736162/viewspace-2152868/
有關審計的更多內容可以參考我的BLOG：http://blog.itpub.net/26736162/viewspace-2140644/
有關資料庫操作導致索引失效的更多內容可以參考我的BLOG：http://blog.itpub.net/26736162/viewspace-2152871/
miaolhr，學習最實用的資料庫技術。
