Sysaux表空間是system表空間的輔助表空間，他主要存放awr快照，審計信息等信息，默認情況下，sysaux表空間在日常使用的過程中會越來越大使用量，那么怎么才能安全有效的清理sysaux表空間呢？

col Tablespace_Name for a11 col Sum_m for 9999 col Max_m for 9999 col Free_Blk_Cnt for 9999 col Sum_Free_m for 9999 col PCT_USED for a8 col PCT_FREE for a10 Select Tablespace_Name,Sum_m,Max_m,Count_Blocks Free_Blk_Cnt,Sum_Free_m,To_Char(100 * Sum_Free_m / Sum_m, '99.9999') || '%' As Pct_Free,100 - To_Char(100 * Sum_Free_m / Sum_m, '99.9999') || '%' As Pct_usedFrom (Select Tablespace_Name, Sum(Bytes) / 1024 / 1024 As Sum_mFrom Dba_Data_FilesGroup By Tablespace_Name)Left Join (Select Tablespace_Name As Fs_Ts_Name,Max(Bytes) / 1024 / 1024 As Max_m,Count(Blocks) As Count_Blocks,Sum(Bytes / 1024 / 1024) As Sum_Free_mFrom Dba_Free_SpaceGroup By Tablespace_Name)On Tablespace_Name = Fs_Ts_NameORDER BY Sum_Free_m / Sum_m;

可以看到sysaux使用空間有260M左右，再去查詢下sysaux表空間里具體存放了什么數據

col Item for a30 col Schema for a20 set lines 200 set pages 100 SELECT occupant_name"Item",round(space_usage_kbytes/1024/1024,3)"Space Used (GB)",schema_name "Schema",move_procedure "MoveProcedure"FROM v$sysaux_occupantsORDER BY 2 Desc;

主要就是AWR的數據占用,長期沒有清理的話,就有可能存在暴增的情況,那么怎么清理空間呢？有兩種方式

方式一

適用于數據量不是很大的情況下,也是oracle官方給出的清理方案,利用存儲過程進行清理(這里有一個問題 就是該存儲過程其實執行的是delete語句,在存儲過程執行過程中,會生成大量的歸檔,需評估以后慎用)

1、首先查出snap_id

select min(snap_id),max(snap_id) from dba_hist_snapshot ;

2、執行存儲過程清除

EXECUTE DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(1, 150);

?

方式二

trucate基表

先查出sysaux基表信息，按照大小排序

col SEGMENT_NAME for a30 select * from (select segment_name,PARTITION_NAME,segment_type,bytes/1024/1024 from dba_segments where tablespace_name='SYSAUX' order by 4 desc) where rownum<=10;

查找WRH$表發現這些基表中有相同的字段snap_id,我們就利用這個字段進行處理，先利用snap_id進行大基表的備份。

select min(snap_id),max(snap_id) from dba_hist_snapshot ;

CREATE TABLE WRH$_ACTIVE_SESSION_HISTORY_B AS SELECT * FROM WRH$_ACTIVE_SESSION_HISTORY WHERE SNAP_ID>150 ;

驗證備份表信息

SELECT COUNT(*) FROM WRH$_ACTIVE_SESSION_HISTORY_B;

刪除大的基表

TRUNCATE TABLE WRH$_ACTIVE_SESSION_HISTORY;

將備份數據恢復到源表

INSERT INTO WRH$_ACTIVE_SESSION_HISTORY SELECT * FROM WRH$_ACTIVE_SESSION_HISTORY_B; COMMIT;

然后驗證下數據是否寫入到了源表中

select count(1) from wrh$_active_session_history;

最后刪除備份臨時表

drop table wrh$_active_session_history_b purge ;

再次查詢表空間使用

col tablespace_name format a12 ; col tablespace_name format a12 ; select t1.tablespace_name "tablespace name",t1.flag "tablespace type",trunc(t1.bytes-nvl(t2.bytes,0),2) "usage(G)", trunc(nvl(t2.bytes,0),2) "free(G)",trunc(t1.bytes,2) "tatal size(G)",100-round(100*nvl(t2.bytes,0)/t1.bytes,2) "usage(%)", round(100*nvl(t2.bytes,0)/t1.bytes,2) "free(%)",trunc(t1.maxbytes,2) "maxextends(G)" from ( SELECT tablespace_name,sum(d1.bytes)/1024/1024/1024bytes,'NORMAL' FLAG, sum(decode(d1.autoextensible,'NO',d1.bytes,d1.maxbytes))/1024/1024/1024 maxbytes FROM dba_data_files d1 GROUP BY tablespace_name UNION all SELECT tablespace_name,sum(d2.bytes)/1024/1024/1024bytes,'TEMP' FLAG, sum(decode(d2.autoextensible,'NO',d2.bytes,d2.maxbytes))/1024/1024/1024 maxbytes FROM dba_temp_files d2 GROUP BY tablespace_name ) t1,( SELECT tablespace_name,sum(f.bytes)/1024/1024/1024 bytes FROM dba_free_space f GROUP BY tablespace_name ) t2 where t1.tablespace_name = t2.tablespace_name(+) ORDER by t1.flag,t1.tablespace_name ;

?

?

總結

以上是生活随笔為你收集整理的sysaux表空间清理的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。