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;