SYSAUX表空間佔用過大情況下的處理（AWR資訊過多）
真題1、 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"
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（Server
Manageability - 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 GLOBAL
INDEXES”子句讓全域性索引不失效。
（二）統計資訊佔用過大
如果統計資訊佔用空間過大，那麼可以修改統計資訊的保留時間。統計資訊預設保留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/
一般來講除開業務資料存放的表空間，DBA要著重關注SYSTEM,SYSAUX,UNDO,TEMP表空間，SYSTEM表空間的大小一般是衡定的，UNDO和TEMP表空間的大小由資料庫的業務情況決定，而SYSAUX表空間在預設條件下你如果不做任何配置，隨著時間的推移，會膨脹的越來越大！SYSAUX表空間做為SYSTEM表空間的輔助表空間，主要存放EM相關的內容以及表統計資訊，AWR快照，審計資訊等，個人認為，如果你的SYSAUX表空間大小超過2G，那麼該考慮讓他減肥了！
查詢語句：
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的保留時間：
SELECT * FROM Dba_Hist_Wr_Control;
發現該庫保留了180天約半年的時間，商量之後將該時間縮短至30天：
exec dbms_workload_repository.modify_snapshot_settings(interval=>30, retention=>30*24*60);
一：使用下列語句查詢表空間使用率
SELECT * FROM (
SELECT D.TABLESPACE_NAME,
SPACE || 'M' "SUM_SPACE(M)",
BLOCKS "SUM_BLOCKS",
SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%'
"USED_RATE(%)",
FREE_SPACE || 'M' "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL
SELECT D.TABLESPACE_NAME,
SPACE || 'M' "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE || 'M' "USED_SPACE(M)",
ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY 1)
WHERE TABLESPACE_NAME IN ('SYSAUX','SYSTEM','UNDOTBS1','TEMP');
二：查詢SYSAUX表空間內各個分類專案佔儲存空間的比重，很明顯可以看出來AWR快照佔用了2G左右的空間，統計資訊為149M左右，同時資料庫關閉了審計audit_trail，所以審計表aud$不佔空間
SELECT occupant_name "Item",
space_usage_kbytes / 1048576 "Space Used (GB)",
schema_name "Schema",
move_procedure "Move Procedure"
FROM v$sysaux_occupants
ORDER BY 1
三：修改統計資訊的保持時間，預設為31天，這裡修改為7天，過期的統計資訊會自動被刪除
SQL> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
31
SQL> exec dbms_stats.alter_stats_history_retention(7);
PL/SQL procedure successfully completed.
SQL> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
7
四：修改AWR快照的儲存時間為7天(7*24*60),每小時收集一次，也可以通過EM介面檢視和修改
SQL> begin
dbms_workload_repository.modify_snapshot_settings (
interval => 60,
retention => 10080,
topnsql => 100
);
end;
五：刪除AWR快照，再次檢視SYSAUX表空間使用率,最後表空間使用率降低為38.42%
select min(snap_id),max(snap_id) from dba_hist_snapshot;//查詢最最小和最大快照ID
begin
dbms_workload_repository.drop_snapshot_range(
low_snap_id => 10758,
high_snap_id => 10900,
dbid => 387090299);
end;
以下指令碼可以用於診斷SYSAUX表空間使用情況
./opatch lsinventory -detail
@?/rdbms/admin/awrinfo
select dbms_stats.get_stats_history_retention from dual;
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;
---sysaux表空間佔用率很高是awr太多？
select * from dba_hist_wr_control;
---30分鐘收集一次，並保留8天時間(單位：分鐘) interval設定0 表示關閉捕獲快照
exec dbms_workload_repository.modify_snapshot_settings(interval=>30,retention=>8*24*60);
ORA-13541: system moving window baseline size (691200) greater than retention (432000)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 174
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 222
ORA-06512: at line 1
----檢視移動視窗基線大小 MOVING_WINDOW_SIZE=8
select * from dba_hist_baseline;
---修改移動視窗基線大小
exec dbms_workload_repository.modify_baseline_window_size(5);
----再執行就不報錯了
exec dbms_workload_repository.modify_snapshot_settings(interval=>30,retention=>5*24*60);
---手動建立一個快照
exec dbms_workload_repository.create_snapshot();
----建立baseline
exec dbms_workload_repository.create_baseline(56,59,'solgle-db-1');
ORA-13506: operation failed due to invalid snapshot range (56, 59)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 281
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 319
ORA-06512: at line 1
----檢視所有快照
select * from sys.wrh$_active_session_history order by sample_time desc;
----以某段快照為例，建立基線
exec dbms_workload_repository.create_baseline(2240,2246,'solgle-db-1');
---手工刪除某階段快照
exec dbms_workload_repository.drop_snapshot_range(low_snap_id=>2248,high_snap_id=>2249,dbid=>1369758280);
----刪除baseline
exec dbms_workload_repository.drop_baseline(baseline_name=>'solgle-db-1',cascade=>true);
若發現statistics統計資訊佔用了SYSAUX上的大量空間，則可以考慮 使用dbms_stats.purge_stats過程實施清理
Modify retention period: DBMS_STATS.ALTER_STATS_HISTORY_RETENTION
Purge old statistics: DBMS_STATS.PURGE_STATS This procedure purges old versions of statistics saved in the dictionary. To run this procedure, you must have the SYSDBA or both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege.
1. Stats Retention is set to 31 days. By Default it is 7 days. I suggest you can consider reducing the retention days to 10.
2. The number of rows exits in the SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY is 42 Million Rows , where the data exists from “09-JUN-11 01.52.06.895132 PM -05:00 ” to “22-AUG-11 02.53.34.754747 PM -05:00”
3. For other tables ie: SYS. WRI$_OPTSTAT_TAB_HISTORY, SYS.WRI$_OPTSTAT_HISTGRM_HISTORY, the data exists for a month, this is just because the retention set to 31.
ACTION PLAN:
=========
A) Purge the Snapshot . Retain data for 10 days and then purge all the other data. (The number of days data to be kept depends on your Business needs)
SQL> SPOOL CHECK1.OUT
1) Try to force the execution of the purge operations :
SQL> alter session set “_swrf_test_action” = 72;
2) Purging snapshots :
SQL> exec dbms_stats.purge_stats(sysdate-&days);
using &days = n, n-1, n-2, …, n-x
3) Then again execute the below set of SQL’s and upload the spool output file
SQL> SELECT MIN(SAVTIME),MAX(SAVTIME) FROM WRI$_OPTSTAT_TAB_HISTORY;
SQL> SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_TAB_HISTORY;
SQL> SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;
SQL> SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;
SQL> SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;
SQL> SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;
SQL> SPOOL OFF
B) Change the No. of Retention days from 31 to 10. For performing the same, use the below command : (The new retention time is specified in minutes.)
SQL> exec dbms_workload_repository.modify_snapshot_settings(retention=>14400);
NOTE : The parameter value is in minutes so 10 daysx 24 hours x 60 minutes = 14400 minutes
C) Check the value of STATISTICS_LEVEL
If the above value is Set to ALL, then consider changing to TYPICAL.
The reason for requesting to change it to TYPICAL , is because statistics_level=ALL will gather lot of additional information in AWR repository which would consume more space.
Most of the cases, if the statistics_level is set to TYPICAL then the growth would be stopped.
Once the above actions performed, please keep us posted on the status of the same
Oracle 11g 清理SYSAUX空間
SYSAUX表空間做為SYSTEM表空間的輔助表空間，主要存放EM相關的內容以及表統計資訊，AWR快照，審計資訊等。今天就碰到了這個問題，資料庫較慢，奇怪的是無法獲取AWR報告。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
--擷取獲取資料庫報告的片段，正常是顯示快照id
輸入 num_days 的值: 1
Listing the last 1 days of Completed Snapshots
--手工生成快照儲存，很明顯是表空間不足
SQL> exec dbms_workload_repository.create_snapshot();
BEGIN dbms_workload_repository.create_snapshot(); END;
*
第 1 行出現錯誤:
ORA-13509: 更新 AWR 表時出錯
ORA-01683: 索引 ORA-01683: 索引 SYS.WRH$_ACTIVE_SESSION_HISTORY_PK 分割槽 WRH$_ACTIVE_1148453265_0 無法通過 8192 (在表空間 SYSAUX 中) 擴充套件
. 分割槽 無法通過 (在表空間 中) 擴充套件
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 99
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 122
ORA-06512: 在 line 1
--查詢SYSAUX表空間的使用情況，消耗37G，快滿了
SQL> SELECT Upper(F.TABLESPACE_NAME) "表空間名",
D.TOT_GROOTTE_MB "表空間大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空間(M)",
To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99')
|| '%' "使用比",
F.TOTAL_BYTES "空閒空間(M)",
F.MAX_BYTES "最大塊(M)"
FROM (SELECT TABLESPACE_NAME,
Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,
Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
and D.tablespace_name = 'SYSAUX';
表空間名 表空間大小(M) 已使用空間(M) 使用比 空閒空間(M) 最大塊(M)
--------- ------------- ------------- -------- ----------- ----------
SYSAUX 37887.98 37865.6 99.94% 22.38 1
--檢視SYSAUX表空間表的使用情況
SQL> select *
from (select segment_name,
segment_type,
bytes / 1024 / 1024
from dba_segments
where tablespace_name = 'SYSAUX'
and bytes / 1024 / 1024 >1000
order by bytes desc);
SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024
------------------------------------- ------------------ ---------------
WRH$_ACTIVE_SESSION_HISTORY TABLE PARTITION 13479
I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX 2590
WRI$_OPTSTAT_HISTGRM_HISTORY TABLE 2242
WRH$_EVENT_HISTOGRAM_PK INDEX PARTITION 1856
WRH$_EVENT_HISTOGRAM TABLE PARTITION 1792
I_WRI$_OPTSTAT_H_ST INDEX 1544
WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION 1472
WRH$_LATCH TABLE PARTITION 1155
--使用dbms_workload_repository.drop_snapshot_range可以刪除歷史資料，怎奈太慢了，半個小時完全沒有反映。通過v$session看到執行的SQL是delete，這種做法無法降低高水位線。
SQL> select min(snap_id),max(snap_id) from dba_hist_snapshot;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
SQL> select min(snap_id),max(snap_id) from dba_hist_active_sess_history;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
1 36768
SQL> begin
dbms_workload_repository.drop_snapshot_range(
low_snap_id => 1,
high_snap_id => 36768,
dbid => 1148453265);
end;
--手工生成truncate，需要在SYS下執行
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;
--執行完成後，看效果
SQL> SELECT Upper(F.TABLESPACE_NAME) "表空間名",
D.TOT_GROOTTE_MB "表空間大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空間(M)",
To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99')
|| '%' "使用比",
F.TOTAL_BYTES "空閒空間(M)",
F.MAX_BYTES "最大塊(M)"
FROM (SELECT TABLESPACE_NAME,
Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,
Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
and D.tablespace_name = 'SYSAUX';
表空間名 表空間大小(M) 已使用空間(M) 使用比 空閒空間(M) 最大塊(M)
--------- ------------- ------------- -------- ----------- ----------
SYSAUX 37887.98 9132.67 24.10% 28755.31 544
不知何時何原因，一個資料庫的AWR不自動刪除了，然後慢慢的SYSAUX表空間滿了，alert日誌也開始報警
其主要是WRH$_ACTIVE_SESSION_HISTORY 的一個分割槽表佔了很多，沒遇到過的問題，總是感覺很緊張，尤其又是生產庫
於是乎，想了個笨方法開始手動刪WRH$_ACTIVE_SESSION_HISTORY的資料，刪的差不多了就開始收縮表，收縮完表空間有地了
可是過段時間又滿了，心想這笨方法可不行，開始找方法
在官方發現文件 387914.1就是說的這個問題
|
WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy (文件 ID 387914.1)
|
In this Document
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.
SYMPTOMS
AWR tables are not being purged according to settings in sys.wrm$_wr_control. Because of this the tables are accumulating more and more rows and the segments associated with these tables become very large.
CAUSE
Oracle decides what rows need to be purged based on the retention policy. There is a special mechanism which is used in the case of the large AWR tables where we store the snapshot data in partitions. One method of purging data from these tables is by removing partitions that only contain rows that have exceeded the retention criteria. During the nightly purge task, we only drop the partition if all the data in the partition has expired. If the partition contains at least one row which, according to the retention policy shouldn't be removed, then the partition won't be dropped and as such the table will contain old data.
If partition splits do not occur (for whatever reason), then we can end up with a situation where we have to wait for the latest entries to expire before the partition that they sit in can be removed. This can mean that some of the older entries can be retained significantly past their expiry date. The result of this is that the data is not purged as expected.
SOLUTION
A potential solution to this issue is to manually split the partitions of the partitioned AWR objects such that there is more chance of the split partition being purged.You will still have to wait for all the rows in the new partitions to reach their retention time but with split partitions there is more chance of this happening. you can manually split the partitions using the following undocumented command:
alter session set "_swrf_test_action" = 72;
To perform a single split of all the AWR partitions.
-
Check the partition details for the offending table before the split:
SELECT owner,
segment_name,
partition_name,
segment_type,
bytes/1024/1024/1024 Size_GB
FROM dba_segments
WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY';
-
Split the partitions so that there is more chance of the smaller partition being purged:
alter session set "_swrf_test_action" = 72;
NOTE: This command will split partitions for ALL partitioned AWR objects. It also initiates a single split; it does not need to be disabled and will need to be repeated if multiple splits are required.
-
Check the partition details for the offending table after the split:
SELECT owner,
segment_name,
partition_name,
segment_type,
bytes/1024/1024/1024 Size_GB
FROM dba_segments
WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY';
With smaller partitions it is expected that some will be automatically removed when the retention period of all the rows within each partition is reached.
As an alternative, you could purge data based upon a snapshot range. Depending on the snapshots chosen, this may remove data that has not yet reached the retention limit so this may not be suitable for all cases.
The following output shows the min and max snapshot_id in each partition.
set serveroutput on
declare
CURSOR cur_part IS
SELECT partition_name from dba_tab_partitions
WHERE table_name = 'WRH$_ACTIVE_SESSION_HISTORY';
query1 varchar2(200);
query2 varchar2(200);
TYPE partrec IS RECORD (snapid number, dbid number);
TYPE partlist IS TABLE OF partrec;
Outlist partlist;
begin
dbms_output.put_line('PARTITION NAME SNAP_ID DBID');
dbms_output.put_line('--------------------------- ------- ----------');
for part in cur_part loop
query1 := 'select min(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||') group by dbid';
execute immediate query1 bulk collect into OutList;
if OutList.count > 0 then
for i in OutList.first..OutList.last loop
dbms_output.put_line(part.partition_name||' Min '||OutList(i).snapid||' '||OutList(i).dbid);
end loop;
end if;
query2 := 'select max(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||') group by dbid';
execute immediate query2 bulk collect into OutList;
if OutList.count > 0 then
for i in OutList.first..OutList.last loop
dbms_output.put_line(part.partition_name||' Max '||OutList(i).snapid||' '||OutList(i).dbid);
dbms_output.put_line('---');
end loop;
end if;
end loop;
end;
/
Once you have split the partitions and identified a partition with a range of snap ids that can be deleted, you can free up the memory by dropping a snapshot range than matches the high and low snap_ids for the partition:
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
low_snap_id IN NUMBER,
high_snap_id IN NUMBER
dbid IN NUMBER DEFAULT NULL);
其方法就是執行alter session set "_swrf_test_action" = 72; 讓AWR相關的分割槽表都建立一個新的分割槽，等待老分割槽的資料過期，oracle就會自動刪除。
可眼下表空間已經滿了，等不到老分割槽的資料過期，可咋辦
那麼可以先修改AWR的有效保留期，比如改成5天。等5天后oracle刪掉分割槽在把保留期改回來。如果老資料需要保留可以匯出AWR的資訊。
流言終結者: AWR的保留天數和SYSAUX表空間的使用率有關嗎？
今天在QQ群的技術討論中有人提及AWR實際保留的天數並非10g的 7天 或 11g 的 8天 ，而是視乎SYSAUX表空間的使用率而定，當SYSAUX表空間空閒空間較多時會將AWR資料保留地更久。
雖然不知道以上這番理論出自那部書籍，但是至少是說的有模有樣的，而且網友還告訴我這是他測試過的結果。
實際是這樣嗎？
我相信這位網友並沒有吹牛，他很可能查詢dba_hist_snapshot等AWR檢視且看到了的確有7天之前的快照仍被保留著，而沒有被清理掉。我們來重演他所看到的現場:
測試使用版本11.2.0.2 ， 11g中預設AWR保留8天：
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> select * from global_name;
GLOBAL_NAME
----------------------------------------------------------
www.askmaclean.com & www.askmaclean.com
SQL> col SNAP_INTERVAL for a20
SQL> col RETENTION for a20
SQL> select snap_interval,retention from dba_hist_wr_control;
SNAP_INTERVAL RETENTION
-------------------- --------------------
+00000 01:00:00.0 +00008 00:00:00.0
以上確認了預設的快照間隔為1小時 ，且保留時間為8天
檢查當前SYSAUX表空間的使用率
REM tablespace report
set linesize 200
select a.tablespace_name,
round(a.bytes_alloc / 1024 / 1024) megs_alloc,
round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,
round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,
100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,
round(maxbytes / 1048576) Max
from (select f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name) a,
(select f.tablespace_name, sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
union all
select h.tablespace_name,
round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
1048576) megs_free,
round(sum(nvl(p.bytes_used, 0)) / 1048576) megs_used,
round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,
100 -
round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
sum(h.bytes_used + h.bytes_free)) * 100) pct_used,
round(sum(f.maxbytes) / 1048576) max
from sys.v_$TEMP_SPACE_HEADER h,
sys.v_$Temp_extent_pool p,
dba_temp_files f
where p.file_id(+) = h.file_id
and p.tablespace_name(+) = h.tablespace_name
and f.file_id = h.file_id
and f.tablespace_name = h.tablespace_name
group by h.tablespace_name
ORDER BY 1
/
TABLESPACE_NAME MEGS_ALLOC MEGS_FREE MEGS_USED PCT_FREE PCT_USED MAX
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
MGMT_AD4J_TS 200 199 1 99 1 32768
MGMT_ECM_DEPOT_TS 40 13 27 32 68 32768
MGMT_TABLESPACE 1350 86 1265 6 94 32768
SYSAUX 600 295 305 49 51 32768
SYSTEM 700 231 469 33 67 32768
TEMP 21 21 0 100 0 32768
UNDOTBS1 495 358 137 72 28 32768
USERS 1950 1243 707 64 36 32768
SYSAUX表空間剩餘295MB空間，空閒率較高
因為這套資料庫在2011-10-17之後就一直沒有開啟過，所以Automatic Workload Repository中最早保留的快照資訊是在2011-10-10，通過查詢dba_hist_snapshot檢視可以反映這一點:
select snap_id,
to_char(begin_interval_time, 'YYYY-MM-DD'),
to_char(end_interval_time, 'YYYY-MM-DD')
from dba_hist_snapshot
order by snap_id;
SNAP_ID TO_CHAR(BE TO_CHAR(EN
---------- ---------- ----------
96 2011-10-10 2011-10-10
97 2011-10-10 2011-10-10
98 2011-10-10 2011-10-10
99 2011-10-10 2011-10-10
100 2011-10-10 2011-10-10
101 2011-10-10 2011-10-11
102 2011-10-11 2011-10-11
103 2011-10-11 2011-10-11
...................
221 2011-10-17 2011-10-17
222 2011-10-24 2011-10-24
SQL> select sysdate from dual;
SYSDATE
---------
24-OCT-11
當前的日期是24-OCT-11，而最早的快照資訊是在2011-10-10，這樣就達成了網友所說的AWR的保留時間並非7或8天，”awr保留天數根據sysaux大小決定。” 或 “預設7天，sysaux足夠大這個7天沒有意義” 的說法。
事實是這樣嗎？
不是的!
那麼為什麼能看到早於7天的快照呢？
回答： 不要被所看到的資訊所矇蔽，雖然我們常說事實勝於雄辯或實踐是檢驗真知的唯一 ， 但事情的表象往往會欺騙我們。
以上這個問題的關鍵點並非在於是否能看到早於7天的snapshot快照資訊，而在於當MMON後臺程式(該程式負責收集和清理AWR資料)在執行對過期快照清理工作時是否會清除7 或 8 天之前的snapshot，以及MMON後臺程式多久才Purge一次AWR Snapshot。
以上這些問題 ， 我們可以通過_swrf_test_action引數和10046 trace搞清楚:
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.inst_id = USERENV ('Instance')
4 AND y.inst_id = USERENV ('Instance')
5 AND x.indx = y.indx
6 AND x.ksppinm LIKE '%_swrf%';
NAME VALUE DESCRIB
-------------------- ---------- --------------------------------------------------
_swrf_test_action 0 test action parameter for SWRF
_swrf_mmon_flush TRUE Enable/disable SWRF MMON FLushing
_swrf_mmon_metrics TRUE Enable/disable SWRF MMON Metrics Collection
_swrf_metric_frequen FALSE Enable/disable SWRF Metric Frequent Mode Collectio
t_mode n
_swrf_on_disk_enable TRUE Parameter to enable/disable SWRF
d
_swrf_mmon_dbfus TRUE Enable/disable SWRF MMON DB Feature Usage
_swrf_test_dbfus FALSE Enable/disable DB Feature Usage Testing
_swrf_test_action 隱藏引數用以除錯MMON的行為，設定該引數並10046事件:
SQL> alter session set "_swrf_test_action" = 28;
Session altered.
SQL> alter session set "_swrf_test_action" = 10;
Session altered.
[oracle@vrh4 ContentsXML]$ ps -ef|grep mmon
oracle 2872 1 0 18:28 ? 00:00:00 ora_mmon_SBDB
oracle 3446 3289 0 18:44 pts/1 00:00:00 tail -f SBDB_mmon_2872.trc
oracle 3997 3407 0 19:17 pts/2 00:00:00 grep mmon
SQL> oradebug setospid 2872;
Oracle pid: 15, Unix process pid: 2872, image: oracle@vrh4.oracle.com (MMON)
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
完成以上操作後等待一段時間，MMON程式的trace檔案會陸續寫出一些資訊，如:
*** 2011-10-24 18:45:24.795
KEWRAFM: Beginning one MMON Auto-Flush cycle ...
Finished one MMON Auto-Flush cycle.
*** 2011-10-24 18:46:24.874
KEWRAFM: Beginning one MMON Auto-Flush cycle ...
Finished one MMON Auto-Flush cycle.
*** 2011-10-24 18:47:24.952
KEWRAFM: Beginning one MMON Auto-Flush cycle ...
Finished one MMON Auto-Flush cycle.
*** 2011-10-24 18:48:25.053
KEWRAFM: Beginning one MMON Auto-Flush cycle ...
Finished one MMON Auto-Flush cycle.
說明MMON每分鐘都會自動重新整理一定的資料到磁碟上。
此外還可以看到MMON清理過期快照的資訊:
*** 2011-10-24 18:58:25.290
KEWRAFM: Beginning one MMON Auto-Flush cycle ...
Finished one MMON Auto-Flush cycle.
KEWRAPM: Beginning one MMON Auto-Purge cycle ... KEWRAPM: Finished one MMON Auto-Purge cycle. KEWRAPC: Auto Purge Action Completed.
*** 2011-10-24 19:28:26.091
KEWRAFM: Beginning one MMON Auto-Flush cycle ...
Finished one MMON Auto-Flush cycle.
KEWRAPM: Beginning one MMON Auto-Purge cycle ..
KEWRAPM: Finished one MMON Auto-Purge cycle
*** 2011-10-24 19:58:27.041
KEWRAFM: Beginning one MMON Auto-Flush cycle ...
Finished one MMON Auto-Flush cycle.
KEWRAPM: Beginning one MMON Auto-Purge cycle ...
KEWRAPM: Finished one MMON Auto-Purge cycle.
可以看到在預設情況下MMON每30分鐘會自動去清理一次Automatic Workload Repository自動負載倉庫中的過期快照資訊，當18:58:25.290的第一次清理工作完成後查詢dba_hist_snapshot可以發現過期快照消失了:
SQL> select snap_id,
2 to_char(begin_interval_time, 'YYYY-MM-DD'),
3 to_char(end_interval_time, 'YYYY-MM-DD')
4 from dba_hist_snapshot
5 order by snap_id;
SNAP_ID TO_CHAR(BE TO_CHAR(EN
---------- ---------- ----------
194 2011-10-16 2011-10-16
195 2011-10-16 2011-10-16
196 2011-10-16 2011-10-16
197 2011-10-16 2011-10-16
.................
222 2011-10-24 2011-10-24
通過以上演示我們可知AWR快照的保留天數與SYSAUX的使用率並無關係，實際控制AWR保留天數的最主要因素是MMON何時、如何地清理過期快照？ MMON的清理操作直接受到dba_hist_wr_control.retention設定值的影響，預設情況10g 為保留7天，而11g為保留8天，MMON只已清理過期的快照。
同時KEWRAPM的trace資訊也說明了,預設情況下MMON每30分鐘做一次”MMON Auto-Purge cycle”清理工作。
二、清理SYAUX下的無效ASH資訊
1.檢查是否有無效的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);
2.清理無效的ASH資訊
delete
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);
3.對ASH表清理後的碎片整理
alter table sys.wrh$_active_session_history enable row movement;
alter table sys.wrh$_active_session_history shrink space cascade;
alter table sys.wrh$_active_session_history disable row movement;
4.收集碎片整理後表的統計資訊
EXEC dbms_stats.gather_table_stats(ownname => ‘SYS’,tabname => ‘WRH$_ACTIVE_SESSION_HISTORY’,cascade => TRUE);
3.檢查表空間可收縮的的位置
select a.FILE#,
a.NAME,
a.BYTES / 1024 / 1024 mb,
ceil(HWM * A.BLOCK_SIZE) / 1024 / 1024 RESIZETO,
‘ALTER DATABASE DATAFILE ”’ || A.NAME || ”’ RESIZE ‘ ||
(trunc(CEIL(HWM * A.BLOCK_SIZE) / 1024 / 1024)+20) || ‘M;’ RESIZECMD
from v$datafile a,
(SELECT C.file_id, MAX(C.block_id + C.blocks – 1) HWM
FROM DBA_EXTENTS C
GROUP BY FILE_ID) B
WHERE A.FILE# = B.FILE_ID
AND a.tablespace=’SYSAUX’
ORDER BY 5;
三.SYSAUX清理後的檢查
1.清理後的無效INDEX檢查
select * from dba_indexes where status<>‘VALID’ AND STATUS<>‘N/A’;
SELECT * FROM DBA_IND_PARTITIONS WHERE STATUS<>‘USABLE’ AND STATUS<>‘N/A’;
SELECT * FROM DBA_IND_SUBPARTITIONS WHERE STATUS<>‘USABLE’;
上面語句應均無資料返回，如有則對這些INDEX進行重建
2.清理後的INDEX並行度檢查
select * from dba_indexes where degree not in (’1′,’0′,’DEFAULT’);
oracle資料庫清理和回收system和sysaux表空間
作者：dbdream
前幾天和一個網友討論了下SYSAUX表空間使用率過高的問題，今天有時間整理一下，正好我們的測試資料庫也存在這個問題。本案例資料庫版本為11.2.0.4.0。
SYSAUX表空間被稱為系統輔助表空間，是10g版本開始推出的新功能，主要的目的是為SYSTEM表空間減負，Oracle對SYSTEM表空間的維護有一套獨立的體系，對SYSTEM表空間操作會佔用額外的CPU資源，而且效率低下，詳見我之前發表的文章為什麼不要把使用者表儲存到SYSTEM表空間。在10g版本，增加了SYSAUX輔助表空間，將EM、AWR等元件的表從SYSTEM表空間挪到了SYSAUX表空間中，這樣大大減少了SYSTEM表空間的消耗，也減少了Oracle對SYSTEM表空間維護的成本。
但是有幾個比較坑爹的元件需要的表並沒有挪到SYSAUX表空間，比如常見的審計用到的AUD$表，很多DBA都可能遇到SYSTEM表空間使用率過高，查詢發現是AUD$表很大導致的，我們的資料庫正好開啟了審計功能，正好可以拿來做實驗。
下面先查詢下SYSTEM和SYSAUX表空間的使用率。
sys@IVLDB> SELECT * FROM (
2 SELECT D.TABLESPACE_NAME,
3 SPACE || 'M' "SUM_SPACE(M)",
4 BLOCKS "SUM_BLOCKS",
5 SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
6 ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%'
7 "USED_RATE(%)",
8 FREE_SPACE || 'M' "FREE_SPACE(M)"
9 FROM ( SELECT TABLESPACE_NAME,
10 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
11 SUM (BLOCKS) BLOCKS
12 FROM DBA_DATA_FILES
13 GROUP BY TABLESPACE_NAME) D,
14 ( SELECT TABLESPACE_NAME,
15 ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE
16 FROM DBA_FREE_SPACE
17 GROUP BY TABLESPACE_NAME) F
18 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
19 UNION ALL
20 SELECT D.TABLESPACE_NAME,
21 SPACE || 'M' "SUM_SPACE(M)",
22 BLOCKS SUM_BLOCKS,
23 USED_SPACE || 'M' "USED_SPACE(M)",
24 ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
25 NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"
26 FROM ( SELECT TABLESPACE_NAME,
27 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
28 SUM (BLOCKS) BLOCKS
29 FROM DBA_TEMP_FILES
30 GROUP BY TABLESPACE_NAME) D,
31 ( SELECT TABLESPACE_NAME,
32 ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
33 ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
34 FROM V$TEMP_SPACE_HEADER
35 GROUP BY TABLESPACE_NAME) F
36 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
37 ORDER BY 1)
38 WHERE TABLESPACE_NAME IN ('SYSAUX','SYSTEM');
TABLESPACE_NAME SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)
----------------- -------------- ---------- --------------- --------------- ---------------
SYSAUX 22156M 2835968 21126.81M 95.35% 1029.19M
SYSTEM 8686M 1111808 8251.7M 94.99% 434.3M
可見，SYSAUX表空間已經使用了21GB左右，SYSTEM表空間已經使用了8GB左右，下面檢視下使用SYSTEM和SYSAUX表空間的比較大的表有哪些。
sys@IVLDB> select * from (
2 select segment_name,sum(bytes)/1024/1024 total_mb,tablespace_name from dba_segments where tablespace_name in
('SYSTEM','SYSAUX') group by segment_name,tablespace_name order by 2 desc)
3 where rownum <=20;
SEGMENT_NAME TOTAL_MB TABLESPACE_NAME
------------------------------------------------- ---------- --------------
AUD$ 6680 SYSTEM
WRH$_ACTIVE_SESSION_HISTORY 5248.0625 SYSAUX
WRH$_EVENT_HISTOGRAM_PK 2499.0625 SYSAUX
WRH$_EVENT_HISTOGRAM 1794.0625 SYSAUX
WRH$_LATCH_MISSES_SUMMARY_PK 905.0625 SYSAUX
WRH$_SQLSTAT 816.0625 SYSAUX
WRH$_LATCH 800.0625 SYSAUX
C_OBJ#_INTCOL# 768 SYSTEM
WRH$_LATCH_MISSES_SUMMARY 760.0625 SYSAUX
WRH$_ACTIVE_SESSION_HISTORY_PK 712.0625 SYSAUX
WRH$_SYSSTAT_PK 672.0625 SYSAUX
WRH$_LATCH_PK 560.0625 SYSAUX
WRH$_SYSSTAT 504.0625 SYSAUX
I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST 456 SYSAUX
WRH$_PARAMETER_PK 454.0625 SYSAUX
WRH$_SEG_STAT 408.0625 SYSAUX
WRH$_PARAMETER 384.0625 SYSAUX
WRH$_SYSTEM_EVENT 368.0625 SYSAUX
WRI$_OPTSTAT_HISTGRM_HISTORY 312 SYSAUX
I_H_OBJ#_COL# 312 SYSTEM
可見，大表大部分都是AUD$和WRH$開頭的AWR基表，AUD$使用SYSTEM表空間，AWR的基表使用SYSAUX表空間，下面再檢視下SYSAUX表空間的使用情況，可以通過v$sysaux_occupants檢視查詢到。
sys@IVLDB> SELECT occupant_name "Item",
2 space_usage_kbytes / 1048576 "Space Used (GB)",
3 schema_name "Schema",
4 move_procedure "Move Procedure"
5 FROM v$sysaux_occupants
6 ORDER BY 1 ;
Item Space Used (GB) Schema Move Procedure
------------------------------ --------------- -------------------- -----------------------------------
AO .038391113 SYS DBMS_AW.MOVE_AWMETA
AUDIT_TABLES 0 SYS DBMS_AUDIT_MGMT.move_dbaudit_tables
AUTO_TASK .000366211 SYS
EM 0 SYSMAN emd_maintenance.move_em_tblspc
EM_MONITORING_USER .001708984 DBSNMP
EXPRESSION_FILTER .003540039 EXFSYS
JOB_SCHEDULER .010498047 SYS
LOGMNR .013061523 SYSTEM SYS.DBMS_LOGMNR_D.SET_TABLESPACE
LOGSTDBY .001342773 SYSTEM SYS.DBMS_LOGSTDBY.SET_TABLESPACE
ORDIM .000427246 ORDSYS ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDDATA .013244629 ORDDATA ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDPLUGINS 0 ORDPLUGINS ordsys.ord_admin.move_ordim_tblspc
ORDIM/SI_INFORMTN_SCHEMA 0 SI_INFORMTN_SCHEMA ordsys.ord_admin.move_ordim_tblspc
PL/SCOPE .001525879 SYS
SDO .064758301 MDSYS MDSYS.MOVE_SDO
SM/ADVISOR .199707031 SYS
SM/AWR 18.8637695 SYS
SM/OPTSTAT 1.14306641 SYS
SM/OTHER .012268066 SYS
SMON_SCN_TIME .008178711 SYS
SQL_MANAGEMENT_BASE .001647949 SYS
STATSPACK 0 PERFSTAT
STREAMS .000976563 SYS
TEXT .003540039 CTXSYS DRI_MOVE_CTXSYS
TSM 0 TSMSYS
ULTRASEARCH 0 WKSYS MOVE_WK
ULTRASEARCH_DEMO_USER 0 WK_TEST MOVE_WK
WM .003417969 WMSYS DBMS_WM.move_proc
XDB .123962402 XDB XDB.DBMS_XDB.MOVEXDB_TABLESPACE
XSAMD .005004883 OLAPSYS DBMS_AMD.Move_OLAP_Catalog
XSOQHIST .038391113 SYS DBMS_XSOQ.OlapiMoveProc
31 rows selected.
可見SM/AWR元件就使用了將近19GB的SYSAUX表空間，也就是說審計和AWR佔用了大量的SYSTEM和SYSAUX表空間，而這些資料是可以定期清理的，都沒有必要保留太長的時間。
下面先清理審計的資料，如果要保留部分AUD$裡面記錄的審計資料，可以把想要的資料插入到一張臨時表，然後直接truncate這張表就可以了，truncate操作會直接回收AUD$佔用的空間。
sys@IVLDB> truncate table AUD$;
Table truncated.
sys@IVLDB> select bytes/1024/1024 from dba_segments where segment_name='AUD$';
BYTES/1024/1024
---------------
5
可見，truncate這張表之後，6680M的空間直接降為5M，釋放了大量的SYSTEM表空間的空間。下面再來回收下SYSAUX表空間，這個相對比較麻煩，也比較耗時。
如上文所示，通過檢視v$sysaux_occupants檢視，可以確定佔用SYSAUX表空間過多的大部分都是AWR的基表，這樣只要刪除部分AWR資料理論上就可以回收一部分SYSAUX表空間，通常AWR的資料都會設定保留期限，10g版本預設保留7天，11g版本預設保留8天，可以通過dba_hist_wr_control檢視來檢視(注：並不是所有DBA開頭的表都是資料字典，也有很多是檢視，dba_hist_wr_control就是檢視)。
sys@IVLDB> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- ------------------- ------------------- ----------
1357933872 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT
大家可能會有疑問了，AWR的資料既然只保留七八天，為什麼還會佔用這麼多的SYSAUX表空間呢？這個問題我個人認為主要有以下兩個原因，首先，AWR刪除過期的資料是通過DELETE操作完成的，這樣就會產生大量的碎片，特別是SYSAUX表空間存在自動擴充套件的資料檔案，而且這個資料檔案沒有擴充套件到最大，還有擴充套件的空間情況下會很明顯，其次就是ASH的資料有些情況下是不受AWR的保留策略影響的，這個從下面的SQL就可以看出。
sys@IVLDB> select count(*) from WRH$_ACTIVE_SESSION_HISTORY;
COUNT(*)
----------
16918966
sys@IVLDB> select min(snap_id),max(snap_id) from wrh$_active_session_history;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
1 15533
可以看到，ASH的資料從第一個快照開始一直都在保留，導致WRH$_ACTIVE_SESSION_HISTORY表很大，使用DBMS_WORKLOAD_REPOSITORY包清理過期或者不需要的AWR資料，可以回收這部分空間。
sys@IVLDB> exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id =>1,high_snap_id => 15500);
PL/SQL procedure successfully completed.
清理了AWR資料之後，你會發現SYSAUX表空間的空間並沒有被回收，使用率還和之前一樣，這是因為清理AWR操作是通過DELETE操作實現的，表的水位線並沒有下降導致的。
sys@IVLDB> SELECT occupant_name "Item",
2 space_usage_kbytes / 1048576 "Space Used (GB)",
3 schema_name "Schema",
4 move_procedure "Move Procedure"
5 FROM v$sysaux_occupants
6 where occupant_name='SM/AWR';
Item Space Used (GB) Schema Move Procedure
-------------- --------------- -------------------- ------------------
SM/AWR 18.8638306 SYS
AWR的資料還是使用將近19GB的空間，查詢SYSTEM和SYSAUX表空間的使用率會發現SYSTEM表空間的使用率已經降低了很多，因為上文TRUNCATE了AUD$表，這張表使用的是SYSTEM表空間，上文刪除了很多AWR的資料，AWR的資料使用SYSAUX表空間，並沒有回收。
sys@IVLDB> SELECT * FROM (
2 SELECT D.TABLESPACE_NAME,
3 SPACE || 'M' "SUM_SPACE(M)",
4 BLOCKS "SUM_BLOCKS",
5 SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
6 ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%'
7 "USED_RATE(%)",
8 FREE_SPACE || 'M' "FREE_SPACE(M)"
9 FROM ( SELECT TABLESPACE_NAME,
10 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
11 SUM (BLOCKS) BLOCKS
12 FROM DBA_DATA_FILES
13 GROUP BY TABLESPACE_NAME) D,
14 ( SELECT TABLESPACE_NAME,
15 ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE
16 FROM DBA_FREE_SPACE
17 GROUP BY TABLESPACE_NAME) F
18 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
19 UNION ALL
20 SELECT D.TABLESPACE_NAME,
21 SPACE || 'M' "SUM_SPACE(M)",
22 BLOCKS SUM_BLOCKS,
23 USED_SPACE || 'M' "USED_SPACE(M)",
24 ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
25 NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"
26 FROM ( SELECT TABLESPACE_NAME,
27 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
28 SUM (BLOCKS) BLOCKS
29 FROM DBA_TEMP_FILES
30 GROUP BY TABLESPACE_NAME) D,
31 ( SELECT TABLESPACE_NAME,
32 ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
33 ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
34 FROM V$TEMP_SPACE_HEADER
35 GROUP BY TABLESPACE_NAME) F
36 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
37 ORDER BY 1)
38 WHERE TABLESPACE_NAME IN ('SYSAUX','SYSTEM');
TABLESPACE_NAME SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)
-------------------- --------------- ---------- --------------- ------------- --------------
SYSAUX 22156M 2835968 21126.81M 95.35% 1029.19M
SYSTEM 8686M 1111808 1990.25M 22.91% 6695.75M
查詢WRH$_ACTIVE_SESSION_HISTORY表會發現資料已經被刪除了很多，從刪除之前的16918966條記錄變為刪除後的4706條記錄，但是表的大小沒變，還是5GB多。
sys@IVLDB> select count(*) from WRH$_ACTIVE_SESSION_HISTORY;
COUNT(*)
----------
4706
下面通過MOVE操作回收這個表的水位線，來回收這部分被刪除資料佔用的空間。這個表是分割槽表，分割槽表不支援表級別的MOVE操作，直接對分割槽表進行MOVE操作會遇到ORA-14511錯誤。
sys@IVLDB> alter table WRH$_ACTIVE_SESSION_HISTORY move;
alter table WRH$_ACTIVE_SESSION_HISTORY move
*
ERROR at line 1:
ORA-14511: cannot perform operation on a partitioned object
檢視這個表的分割槽資訊，只有兩個分割槽。
sys@IVLDB> select segment_name,partition_name,bytes/1024/1024/1024 gb from dba_segments where segment_name='WRH$_ACTIVE_SESSION_HISTORY';
SEGMENT_NAME PARTITION_NAME GB
------------------------------ ------------------------------ ----------
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1357933872_0 5.125
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN .000061035
下面按照分割槽進行MOVE操作，來回收空間。
sys@IVLDB> alter table WRH$_ACTIVE_SESSION_HISTORY move partition WRH$_ACTIVE_1357933872_0;
Table altered.
sys@IVLDB> alter table WRH$_ACTIVE_SESSION_HISTORY move partition WRH$_ACTIVE_SES_MXDB_MXSN;
Table altered.
對分割槽表進行MOVE之後，需要重建索引，檢視這個表的索引資訊。
sys@IVLDB> select index_name from dba_indexes where table_name='WRH$_ACTIVE_SESSION_HISTORY';
INDEX_NAME
------------------------------
WRH$_ACTIVE_SESSION_HISTORY_PK
這個表只有一個主鍵，而且是分割槽索引，也不能對分割槽索引直接進行REBUILD操作，否則會遇到ORA-14086錯誤。
sys@IVLDB> alter index WRH$_ACTIVE_SESSION_HISTORY_PK rebuild;
alter index WRH$_ACTIVE_SESSION_HISTORY_PK rebuild
*
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole
下面檢視下這個索引的分割槽資訊。
sys@IVLDB> select partition_name from dba_ind_partitions where index_name='WRH$_ACTIVE_SESSION_HISTORY_PK';
PARTITION_NAME
------------------------------
WRH$_ACTIVE_1357933872_0
WRH$_ACTIVE_SES_MXDB_MXSN
然後按照分割槽進行重建索引。
sys@IVLDB> alter index WRH$_ACTIVE_SESSION_HISTORY_PK rebuild partition WRH$_ACTIVE_1357933872_0;
Index altered.
sys@IVLDB> alter index WRH$_ACTIVE_SESSION_HISTORY_PK rebuild partition WRH$_ACTIVE_SES_MXDB_MXSN;
Index altered.
再次檢視，WRH$_ACTIVE_SESSION_HISTORY的空間已經回收。
sys@IVLDB> select sum(bytes)/1024/1024 from dba_segments where segment_name='WRH$_ACTIVE_SESSION_HISTORY';
SUM(BYTES)/1024/1024
--------------------
2.0625
sys@IVLDB> select sum(bytes)/1024/1024 from dba_segments where segment_name='WRH$_ACTIVE_SESSION_HISTORY_PK';
SUM(BYTES)/1024/1024
--------------------
.3125
可見，這個表大小由之前的5248.0625MB將為2.0625MB，重建索引後，索引的大小也由之前的712.0625MB降為了0.3125MB，這一張表就回收了6GB左右的空間。
sys@IVLDB> SELECT occupant_name "Item",
2 space_usage_kbytes / 1048576 "Space Used (GB)",
3 schema_name "Schema",
4 move_procedure "Move Procedure"
5 FROM v$sysaux_occupants
6 where occupant_name='SM/AWR';
Item Space Used (GB) Schema Move Procedure
-------------- --------------- -------------------- ------------------
SM/AWR 13.0436401 SYS
通過v$sysaux_occupants檢視，可以查詢到AWR佔用空間由之前的將近19GB降為了不到13GB。按照同樣的方法，回收下WRH$_EVENT_HISTOGRAM表的空間，這也是分割槽表表1794MB，只有一個索引(是主鍵)2499MB。
sys@IVLDB> select partition_name from dba_tab_partitions where table_name='WRH$_EVENT_HISTOGRAM';
PARTITION_NAME
------------------------------
WRH$_EVENT_HISTO_MXDB_MXSN
WRH$_EVENT__1357933872_0
sys@IVLDB> alter table WRH$_EVENT_HISTOGRAM move partition WRH$_EVENT_HISTO_MXDB_MXSN;
Table altered.
sys@IVLDB> alter table WRH$_EVENT_HISTOGRAM move partition WRH$_EVENT__1357933872_0;
Table altered.
sys@IVLDB> select index_name from dba_indexes where table_name='WRH$_EVENT_HISTOGRAM';
INDEX_NAME
------------------------------
WRH$_EVENT_HISTOGRAM_PK
sys@IVLDB> select partition_name from dba_ind_partitions where index_name='WRH$_EVENT_HISTOGRAM_PK';
PARTITION_NAME
------------------------------
WRH$_EVENT_HISTO_MXDB_MXSN
WRH$_EVENT__1357933872_0
sys@IVLDB> alter index WRH$_EVENT_HISTOGRAM_PK rebuild partition WRH$_EVENT_HISTO_MXDB_MXSN;
Index altered.
sys@IVLDB> alter index WRH$_EVENT_HISTOGRAM_PK rebuild partition WRH$_EVENT__1357933872_0;
Index altered.
清理WRH$_ACTIVE_SESSION_HISTORY和WRH$_EVENT_HISTOGRAM兩張表，SYSAUX表空間就釋放出10GB的空間，其他佔用SYSAUX空間比較大的表也可以安裝這樣的方法去釋放空間，這裡就不一一演示了。
可能有人會問了，既然已經刪除了資料，那麼如果不回收這部分空間，ORACLE就不會再使用這部分空間了嗎？為什麼非要回收呢？這個高水位線的問題主要影響以下幾個方面，不只是使用SYSAUX表空間的表，對所有碎片較多的表都適用。
1.影響查詢速度，因為這樣的表本身比較大，索引也會很大，查詢會很慢。
2.消耗資源，因為表和索引都很大，查詢時會消耗很多I/O資源。
3.空間佔用，雖然大部分資料被DELETE掉了，但是這部分空間仍舊是這個段(SEGMENT)的區(EXTENT)，即使可以再利用也只能是這個表的新增資料才可以使用，而且還得是所在表空間無法自動擴充套件或者沒有足夠的擴充套件空間的情況下，如果表空間可以自動擴充套件或者有足夠的擴充套件空間，那麼還是不會使用這部分空間的，這就導致表和索引會越來越大，佔用的空間越來越大，而一旦將可擴充套件的空間用盡，那麼其他表將無法擴充套件，DELETE掉的資料空間，其他物件是無法使用的。
除了AWR的保留策略會影響SYSAUX表空間的使用率外，AWR收集資料的級別也對SYSAUX表空間的使用率影響很大，AWR收集資料的級別由statistics_level引數控制，這個引數有三個值，BASIC、TYPICAL、ALL，BASIC表示關閉統計資訊收集，TYPICAL表示普通收集級別，只收集夠日常用的統計資訊，ALL是最給力的，凡是ORACLE能收集的所有資訊都要收集，所以生成資料量會很大，相對來說，對效能和佔用空間的影響也是最大的，通常TYPICAL就已經夠用了。
不同的版本statistics_level引數的預設值不同，有的版本預設值為ALL，有的版本預設值為TYPICAL，具體哪些版本使用ALL為預設值，我記不清了，如果您的資料庫設定statistics_level引數的值為ALL，建議調整為TYPICAL。
sys@IVLDB> show parameter statistics_level
NAME TYPE VALUE
------------------------------------ ----------- ---------
statistics_level string TYPICAL
如果您的資料庫也遇到了SYSAUX表空間很大的情況，建議在清理AWR資料時，回收這部分空間，對SYSAUX表空間的物件操作，基本不會影響資料庫的正常使用，SYSAUX表空間存放的物件都是資料庫執行非必須的物件，技術這個表空間損壞或者丟失，資料庫一樣可以正常執行。
About Me
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2152868/，如需轉載，請註明出處，否則將追究法律責任。
