ORACLE 清理SYSAUX表空間

ORACLE 清理SYSAUX表空間

在資料庫檢查中發現SYSAUX表空間占用過大，SYSAUX是ORACLE10G開始提供的功能，用于資料庫為SYSTEM表空間減負。

用以下語句查出相應的表空間值

select

a.tablespace_name,trunc(sum(a.bytes)/1024/1024/1024,2) total,

trunc(sum(a.bytes)/1024/1024/1024 - sum(b.bytes)/1024/1024/1024,2) used,

trunc(sum(b.bytes)/1024/1024/1024,2) free,

to_char(trunc((sum(a.bytes)/1024/1024/1024-sum(b.bytes)/1024/1024/1024)/(sum(a.bytes)/1024/1024/1024),4)*100)||'%' pused,

to_char(trunc((sum(b.bytes)/1024/1024/1024)/(sum(a.bytes)/1024/1024/1024),4)*100)||'%' pfree

from (select sum(bytes) bytes,tablespace_name from dba_data_files

group by tablespace_name) a,(select sum(bytes) bytes,tablespace_name

from dba_free_space group by tablespace_name) b

where a.tablespace_name=b.tablespace_name(+)

group by a.tablespace_name;

查出表空SYSAUX占用率過高

SYSAUX共13.84G 其使用率95%

通過以下語句查出什麼使用這麼多空間

SELECT occupant_name "Item",

space_usage_kbytes / 1048576 "Space Used (GB)",

schema_name "Schema",

move_procedure "Move Procedure"

FROM v$sysaux_occupants

ORDER BY 2 desc;

 從上圖可以看到其中AWR用了11G空間

檢視下AWR統計數的儲存天數

select dbms_stats.get_stats_history_retention from dual; 

 通過 select dbid, min(snap_id),max(snap_id) from dba_hist_snapshot group by dbid;

查出相應的DBID和SNAP_ID,

• 清空上一個dbid下的所有snapshot

exec dbms_workload_repository.drop_snapshot_range(29737,29943,310691130);

等待太久了‘

為了加快清除速實施以下操作

查找到那些占用sysaux表空間的基表，按照大小進行排序

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$ACTIVE_SESSION_HISTOR,WRH$_SQLSTAS,WRH$_EVENT_HISTOGRAM

create table WRH$_ACTIVE_SESSION_HISTORY0926 as select * from WRH$_ACTIVE_SESSION_HISTORY;

create table WRH$_SQLSTAT0926 as select * from WRH$_SQLSTAT;

create table WRH$_EVENT_HISTOGRAM0926 as select * from WRH$_EVENT_HISTOGRAM;

create table WRH$_LATCH0926 as select * from WRH$_LATCH;

清除相應基表資料

truncate  table  WRH$_ACTIVE_SESSION_HISTORY;

truncate  table  WRH$_EVENT_HISTOGRAM;

truncate  table  WRH$_SQLSTAT;

truncate  table  WRH$_LATCH_MISSES_SUMMARY;

truncate  table  WRH$_LATCH;

truncate  table  WRH$_SYSSTAT;

truncate  table  WRH$_SEG_STAT;

truncate  table  WRH$_PARAMETER;

truncate  table  WRH$_SYSTEM_EVENT;

truncate  table  WRH$_SQL_PLAN;

truncate  table  WRH$_DLM_MISC;

truncate  table  WRH$_SERVICE_STAT;

truncate  table  WRH$_TABLESPACE_STAT;

truncate  table  WRH$_ROWCACHE_SUMMARY;

truncate  table  WRH$_MVPARAMETER;