导航

SYSAUX表空间清理

Posted on 2017-02-27 10:08  datalife  阅读(4929)  评论(1编辑  收藏

最近zabbix告警某业务sysaux表空间使用率超过95%,使用sql查看sysaux表空间设置了32G,32G的表空间竟然使用了95%。一般来讲除开业务数据存放的表空间,DBA要着重关注SYSTEM,SYSAUX,UNDO,TEMP表空间,SYSTEM表空间的大小一般是衡定的,UNDO和TEMP表空间的大小由数据库的业务情况决定,而SYSAUX表空间在默认条件下你如果不做任何配置,随着时间的推移,会膨胀的越来越大!SYSAUX表空间做为SYSTEM表空间的辅助表空间,主要存放EM相关的内容以及表统计信息,AWR快照,审计信息等,那么怎么清理sysaux表空间呢?

1、查询表空间使用率

set line 200 pagesize 9999
col tablespace_name for a20
select b.tablespace_name,round(sum(b.bytes)/1024/1024,0) sum_MB,
round(sum(b.bytes)/1024/1024,0)-round(sum(nvl(a.bytes,0))/1024/1024,0) use_MB,
round(sum(nvl(a.bytes,0))/1024/1024,0) free_MB,
round((sum(b.bytes)-sum(nvl(a.bytes,0)))/sum(b.bytes),4)*100 use_precent
from (select tablespace_name,file_id,sum(bytes) bytes from dba_free_space group by tablespace_name,file_id) a,
dba_data_files b
where a.file_id(+)=b.file_id and a.tablespace_name(+)=b.tablespace_name
group by b.tablespace_name
union all
select b.tablespace_name,round(b.bytes/1024/1024,0) sum_MB,
round(nvl(a.bytes,0)/1024/1024,0) use_MB,
round(b.bytes/1024/1024,0)-round(nvl(a.bytes,0)/1024/1024,0) free_MB,
round(nvl(a.bytes,0)/b.bytes,4)*100 use_precent
from (select tablespace_name,sum(nvl(bytes_used,0)) bytes from gv$temp_extent_pool group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes from dba_temp_files group by tablespace_name)b
where a.tablespace_name(+)=b.tablespace_name
order by use_precent desc;

经查询发现sysaux表空间使用率确实查过95%

2、查询SYSAUX表空间内各个分类项目占存储空间的比重,很明显可以看出来AWR快照占用了27G左右统计信息占了276M左右。查询语句如下:

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 ;

3、修改统计信息的保持时间,默认为31天,这里修改为7天,过期的统计信息会自动被删除

select dbms_stats.get_stats_history_retention from dual; 

exec dbms_stats.alter_stats_history_retention(7);

select dbms_stats.get_stats_history_retention from dual; 

4、修改AWR快照的保存时间为7天(7*24*60),每小时收集一次

begin 

         dbms_workload_repository.modify_snapshot_settings ( interval => 60, retention => 10080, topnsql => 100); 

end; 

5、删除AWR快照,最后再次查看SYSAUX表空间使用率

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;