Oracle資料表空間使用率過大問題處理方法

標籤：oracle   資料表空間過大   system資料表空間   sysaux資料表空間   

一、 查看Oracle資料庫內資料表空間使用方式

SELECT a.tablespace_name "資料表空間名",         total "資料表空間大小",         free "資料表空間剩餘大小",         (total - free) "資料表空間使用大小",         total/(1024*1024*1024) "資料表空間大小(G)",         free/(1024*1024*1024) "資料表空間剩餘大小(G)",         (total - free)/(1024*1024*1024) "資料表空間使用大小(G)",         ROUND((total - free) / total, 4) * 100 "使用率 %"  FROM (SELECT tablespace_name, SUM(bytes) free            FROM DBA_FREE_SPACE           GROUP BY tablespace_name) a,         (SELECT tablespace_name, SUM(bytes) total           FROM DBA_DATA_FILES          GROUP BY tablespace_name) b  WHERE a.tablespace_name = b.tablespace_name;

二、 system資料表空間過大（使用率95%以上）

1） 檢查aud$表是大小

--查看資料庫表大小SQLselect bytes,owner,segment_name from dba_segments where segment_type='TABLE' order by bytes desc;--查看aud$表大小SQLselect bytes,owner,segment_name from dba_segments where segment_type='TABLE' and segment_name = 'AUD$';

2） 如果aud$過大，清理之

        匯出aud$表後，用truncate清理

3） 如果出現aud$表為空白,system資料表空間使用率照樣達到99%，建議增加資料檔案

        a)  增加資料檔案樣本語句

            批註：增加的資料表空間大小自己擬定

alter tablespace system add datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SGTMS\SYSTEM02.DBF' size 50M autoextend on

b) 查詢驗證檔案已成功添加

select * from dba_data_files;

三、 sysaux資料表空間（使用率95%以上）

1） 修改統計資訊儲存時間

        a) 檢查統計資訊儲存時間（預設應該是31天）

select dbms_stats.get_stats_history_retention from dual;

        b) 如果31天將其改為7天

exec dbms_stats.alter_stats_history_retention(7);

        c) 驗證是否修改成功

select dbms_stats.get_stats_history_retention from dual;

2） 刪除AWR報告快照

        批註：Oracle 10g中快照會保留7天，11g的快照保留8天，超出會自動刪除。AWR快照可以從其他資料庫匯入，而這部分資料會儲存時間極長。有時候也會遇到自動快照不能自動收集，而手工建立的快照又可以成功，對於這種情況就需要把以前的快照清理掉。

            刪除AWR有兩種方式進行刪除：dbms_workload_repository，dbms_swrf_internal。dbms_workload_repository可以刪除本地和其他資料庫的快照，可以選擇不同的快照來進行刪除；dbms_swrf_internal只能對其他資料庫的快照來進行操作，會把所有的快照直接幹掉。

        a) 使用dbms_workload_repository包刪除

 select dbid, retention from dba_hist_wr_control; select min(snap_id), max(snap_id)from dba_hist_snapshot where dbid = '得到的dbid值'; exec dbms_workload_repository.drop_snapshot_range('得到的min(snap_id)值','得到的max(snap_id)值','得到的dbid值'); select * from dba_hist_snapshot where dbid = '得到的dbid值';

        b) 使用dbms_swrf_internal包刪除

 select dbid, retention from dba_hist_wr_control; select min(snap_id), max(snap_id)from dba_hist_snapshot where dbid = '得到的dbid值'; exec dbms_swrf_internal.unregister_database('得到的dbid值'); select * from dba_hist_snapshot where dbid = '得到的dbid值';

       

Oracle資料表空間使用率過大問題處理方法