【DB筆試面試435】SYSTEM和SYSAUX表空間存儲的內容有哪些區別？若SYSAUX表空間佔用過大則應該如何處理？

• 2019 年 10 月 10 日
• 筆記

題目 SYSTEM和SYSAUX表空間存儲的內容有哪些區別？若SYSAUX表空間佔用過大則應該如何處理？

A

答案

在一般情況下，企業產生的業務數據應該存放在單獨的數據表空間，而不應該使用系統已存在的表空間，尤其不能將業務數據保存到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"    FROM V$SYSAUX_OCCUPANTS   WHERE SPACE_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    FROM DBA_SEGMENTS D   WHERE D.TABLESPACE_NAME = 'SYSAUX'   GROUP BY D.SEGMENT_NAME, D.SEGMENT_TYPE   ORDER BY SIZE_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) FROM DBA_HIST_SNAPSHOT;  SELECT MIN(SNAP_ID),MAX(SNAP_ID) FROM DBA_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    from dba_segments s   where s.segment_name like 'WRH$%'     and segment_type in ('TABLE PARTITION', 'TABLE')     and s.bytes/1024/1024>100     order by s.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 MOVE PARTITION 分區名稱;  

執行完MOVE操作後，需要對索引進行重建。同理，對於分區索引，只能對分區的單個索引進行重建，而不能總體重建：

ALTER INDEX WRH$_ACTIVE_SESSION_HISTORY_PK REBUILD PARTITION 分區名稱;  

需要注意的是，可以在以上SQL後加上「UPDATE GLOBALINDEXES」子句讓全局索引不失效。

（二）統計信息佔用過大

如果統計信息佔用空間過大，那麼可以修改統計信息的保留時間。統計信息默認保留31天，過期的統計信息會自動被刪除。

SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM DUAL;  --查詢統計信息的保留時間  EXEC DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(7);  --設置統計信息的保留時間  

若發現統計信息佔用了SYSAUX上的大量空間，則可以考慮使用DBMS_STATS.PURGE_STATS過程實施清理。

以下的SQL語句對於診斷SYSAUX表空間的佔用情況非常有用：

SELECT DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY FROM DUAL;  SELECT MIN(SAVTIME), MAX(SAVTIME) FROM WRI$_OPTSTAT_TAB_HISTORY;  SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_IND_HISTORY;  SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;  SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;  SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_AUX_HISTORY;  SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_TAB_HISTORY;  SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_IND_HISTORY;  SELECT COUNT(*) FROM SYS.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(*)    FROM SYS.WRH$_ACTIVE_SESSION_HISTORY A   WHERE NOT EXISTS (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/

在日常的數據庫維護中，經常出現SYSTEM表空間被撐滿，在絕大多數情況下是因為數據庫登錄審計的功能被啟動了，此時一般建議把SYS.AUD$相關對象遷移到其它表空間，從而避免SYSTEM被用完的風險。

在Oracle 11g之前遷移方法如下所示：

ALTER TABLE SYS.AUDIT$ MOVE TABLESPACE USERS;  ALTER TABLE SYS.AUDIT_ACTIONS MOVE TABLESPACE USERS;  ALTER TABLE SYS.AUD$ MOVE TABLESPACE USERS;  ALTER TABLE SYS.AUD$ MOVE LOB(SQLBIND) STORE AS SYS_IL0000000384C00041$$ (TABLESPACE USERS);  ALTER TABLE SYS.AUD$ MOVE LOB(SQLTEXT) STORE AS SYS_IL0000000384C00041$$ (TABLESPACE USERS);  ALTER INDEX SYS.I_AUDIT REBUILD ONLINE TABLESPACE USERS;  ALTER INDEX SYS.I_AUDIT_ACTIONS REBUILD ONLINE TABLESPACE USERS;  

從Oracle 11g開始可以使用DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION進行遷移：

EXEC DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(AUDIT_TRAIL_TYPE=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,AUDIT_TRAIL_LOCATION_VALUE => 'USERS');  

如下示例為授予審計的一些權限：

GRANT AUDIT ANY TO LHR_TEST;  GRANT AUDIT SYSTEM TO LHR_TEST;  

在Oracle 11g之前通過手工清理的方式或自定義作業來定期清理SYS.AUD$表，如下：

TRUNCATE TABLE SYS.AUD$;  DELETE FROM SYS.AUD$ WHERE OBJ$NAME='EMP';  

需要注意的是，如果AUD$表過大，那麼直接TRUNCATE AUD$表，系統要立即釋放大量的EXTENTS，會嚴重影響系統性能。可以通過如下2個步驟逐步釋放EXTENTS：

①　清空數據並且保留原來的EXTENTS：

TRUNCATE TABLE SYS.AUD$ REUSE STORAGE;  

在這裡，REUSE STORAGE是TRUNCATE的一個參數，表示保持原來的存儲不變。一般情況下，SQL命令「TRUNCATE TABLE TABLE_NAME;」其實就是「TRUNCATE TABLE TABLE_NAME DROP STORAGE;」。DROP STORAGE是TRUNCATE TABLE的默認參數。

②　逐步回縮EXTENTS：

ALTER TABLE SYS.AUD$ DEALLOCATE UNUSED KEEP 5000M;  ALTER TABLE SYS.AUD$ DEALLOCATE UNUSED KEEP 2000M;  ……  ALTER TABLE SYS.AUD$ DEALLOCATE UNUSED KEEP 10M;  

需要注意的是，在執行的時候，可以根據實際情況調整每次回縮空間的大小。

若審計在OS和XML選項下進行手動刪除審計文件。在Oracle 11g中通過DBMS_AUDIT_MGMT包下的子過程進行手動或定期清理。下面的過程可以遷移審計記錄到USERS表空間：

conn / as sysdba  BEGIN    DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(AUDIT_TRAIL_TYPE           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,                                             AUDIT_TRAIL_LOCATION_VALUE => 'USERS');    END;  /  

使用包DBMS_AUDIT_MGMT下的INIT_CLEANUP過程可以設置審計的清除間隔，還有很多其它的實用存儲過程請參考官方文檔。

& 說明：

有關審計的更多內容可以參考我的BLOG：http://blog.itpub.net/26736162/viewspace-2140644/