SYSAUX資料表空間使用率高問題處理

一般來講除開業務資料存放的資料表空間，DBA要著重關注SYSTEM,SYSAUX,UNDO,TEMP資料表空間，SYSTEM資料表空間的大小一般是衡定的，UNDO和TEMP資料表空間的大小由資料庫的業務情況決定，而SYSAUX資料表空間在預設條件下你如果不做任何配置，隨著時間的推移，會膨脹的越來越大！SYSAUX資料表空間做為SYSTEM資料表空間的輔助資料表空間，主要存放EM相關的內容以及表統計資訊，AWR快照，審計資訊等，個人認為，如果你的SYSAUX資料表空間大小超過2G，那麼該考慮讓他減肥了！ 

一：使用下列語句查詢資料表空間使用率 

 

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

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/1R5593234-0.jpg" border="0" alt="" />二：查詢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 
 

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/1R5593L9-1.jpg" border="0" alt="" />

三：修改統計資訊的保持時間，預設為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; 
 

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/1R5592138-2.jpg" border="0" alt="" />

五：刪除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; 
 

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/1R55a919-3.jpg" border="0" alt="" />

參考文章，感謝作者分享！http://jhdba.wordpress.com/2009/05/19/purging-statistics-from-the-sysaux-tablespace/

本文出自 “斬月” 部落格，謝絕轉載！