概述

SYSAUX表空間被稱為系統(tǒng)輔助表空間，是10g版本開始推出的新功能，主要的目的是為SYSTEM表空間減負(fù)，Oracle對(duì)SYSTEM表空間的維護(hù)有一套獨(dú)立的體系，對(duì)SYSTEM表空間操作會(huì)占用額外的CPU資源，而且效率低下。在10g版本，增加了SYSAUX輔助表空間，將EM、AWR等組件的表從SYSTEM表空間挪到了SYSAUX表空間中，這樣大大減少了SYSTEM表空間的消耗，也減少了Oracle對(duì)SYSTEM表空間維護(hù)的成本。

[[283613]]

下面介紹下最近清理sysaux表空間的過程，僅供參考。

1. 查詢SYSTEM和SYSAUX表空間的使用率

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'); 

可見，SYSAUX表空間已經(jīng)使用了43GB左右，SYSTEM表空間已經(jīng)使用了3GB左右。

2. 查看下使用SYSTEM和SYSAUX表空間的比較大的表

select * 
 from (select segment_name,PARTITION_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 3 desc) 
 where rownum <= 20; 

可見，大表大部分都是AUD$和WRH$開頭的AWR基表，AUD$使用SYSTEM表空間，AWR的基表使用SYSAUX表空間

3. 查看SYSAUX表空間的具體使用情況

這里可以通過v$sysaux_occupants視圖查詢到

SELECT occupant_name "Item", 
 space_usage_kbytes / 1048576 "Space Used (GB)", 
 schema_name "Schema", 
 move_procedure "Move Procedure" 
 FROM v$sysaux_occupants 
 ORDER BY space_usage_kbytes desc; 

可見SM/AWR組件就使用了40GB的SYSAUX表空間，也就是說審計(jì)和AWR占用了大量的SYSTEM和SYSAUX表空間，而這些數(shù)據(jù)是可以定期清理的，都沒有必要保留太長的時(shí)間。

通過查看v$sysaux_occupants視圖，可以確定占用SYSAUX表空間過多的大部分都是AWR的基表，這樣只要?jiǎng)h除部分AWR數(shù)據(jù)理論上就可以回收一部分SYSAUX表空間，通常AWR的數(shù)據(jù)都會(huì)設(shè)置保留期限，10g版本默認(rèn)保留7天，11g版本默認(rèn)保留8天，可以通過dba_hist_wr_control視圖來查看(注：并不是所有DBA開頭的表都是數(shù)據(jù)字典，也有很多是視圖，dba_hist_wr_control就是視圖)

問題來了，AWR的數(shù)據(jù)既然只保留七八天，為什么還會(huì)占用這么多的SYSAUX表空間呢?這個(gè)問題其實(shí)有以下兩個(gè)原因，首先，AWR刪除過期的數(shù)據(jù)是通過DELETE操作完成的，這樣就會(huì)產(chǎn)生大量的碎片，特別是SYSAUX表空間存在自動(dòng)擴(kuò)展的數(shù)據(jù)文件，而且這個(gè)數(shù)據(jù)文件沒有擴(kuò)展到最大，還有擴(kuò)展的空間情況下會(huì)很明顯，其次就是ASH的數(shù)據(jù)有些情況下是不受AWR的保留策略影響的。

4. 使用dbms_workload_repository.drop_snapshot_range刪除歷史數(shù)據(jù)(不推薦)

--ASH的數(shù)據(jù)從第一個(gè)快照開始一直都在保留，導(dǎo)致WRH$_ACTIVE_SESSION_HISTORY表很大，使用DBMS_WORKLOAD_REPOSITORY包 
--清理過期或者不需要的AWR數(shù)據(jù)，可以回收這部分空間 
select count(*) from WRH$_ACTIVE_SESSION_HISTORY; 
select min(snap_id),max(snap_id) from wrh$_active_session_history; 
exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id =>1,high_snap_id => 25100); 
select count(*) from WRH$_ACTIVE_SESSION_HISTORY; 

清理了AWR數(shù)據(jù)之后，可以發(fā)現(xiàn)SYSAUX表空間的空間并沒有被回收，使用率還和之前一樣，這是因?yàn)榍謇鞟WR操作是通過DELETE操作實(shí)現(xiàn)的，表的水位線并沒有下降導(dǎo)致的。

5. 手工生成truncate，需要在SYS下執(zhí)行

select distinct 'truncate table ' || segment_name || ';', 
 s.bytes/1024/1024 MB 
 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 desc; 

實(shí)際執(zhí)行sql:通過上面語句可以看到基本上都是以WRH$_開頭的段，這些類型的段基本上都是與AWR相關(guān)的,以下均以sys用戶執(zhí)行

truncate table WRH$_SQL_BIND_METADATA; —-保存AWR收集SQL綁定信息表 
truncate table WRH$_ACTIVE_SESSION_HISTORY; ——保存AWR收集歷史會(huì)話信息表 
select segment_name,partition_name,bytes/1024/1024/1024 gb from dba_segments where segment_name in('WRH$_LATCH','WRH$_SQLSTAT'); 
alter table WRH$_LATCH truncate partition WRH$_LATCH_1013373590_0; 
alter table WRH$_SQLSTAT truncate partition WRH$_SQLSTA_1013373590_0; 
truncate table WRI$_ADV_SQLT_PLANS; —保存AWR收集SQL建議計(jì)劃信息表 
alter table WRH$_SYSSTAT shrink space; 
alter index WRH$_SYSSTAT_PK shrink space; 
alter table WRH$_LATCH shrink space; 
alter table WRH$_SEG_STAT shrink space; 
alter table WRH$_SQLSTAT shrink space; 
alter table WRH$_PARAMETER shrink space; —-保存AWR收集參數(shù)信息表 
alter index WRH$_PARAMETER_PK shrink space;  
truncate table WRH$_EVENT_HISTOGRAM; 
truncate table WRH$_SQL_PLAN; —-保存AWR收集的SQL執(zhí)行計(jì)劃表 
truncate table WRH$_SQLTEXT; –—保存AWR收集的SQL文本表 

6. 驗(yàn)證

達(dá)到需求，完。