欢迎大家来到IT世界,在知识的湖畔探索吧!

概述

SYSAUX表空间被称为系统辅助表空间，是10g版本开始推出的新功能，主要的目的是为SYSTEM表空间减负，Oracle对SYSTEM表空间的维护有一套独立的体系，对SYSTEM表空间操作会占用额外的CPU资源，而且效率低下，详见我之前发表的文章为什么不要把用户表存储到SYSTEM表空间。在10g版本，增加了SYSAUX辅助表空间，将EM、AWR等组件的表从SYSTEM表空间挪到了SYSAUX表空间中，这样大大减少了SYSTEM表空间的消耗，也减少了Oracle对SYSTEM表空间维护的成本。

下面介绍下最近清理sysaux表空间的过程，仅供参考。

1、查询SYSTEM和SYSAUX表空间的使用率

SELECT *
 FROM (SELECT D.TABLESPACE_NAME,
 SPACE || 'M' "SUM_SPACE(M)",
 BLOCKS "SUM_BLOCKS",
 SPACE - NVL(FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
 ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) || '%' "USED_RATE(%)",
 FREE_SPACE || 'M' "FREE_SPACE(M)"
 FROM (SELECT TABLESPACE_NAME,
 ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
 SUM(BLOCKS) BLOCKS
 FROM DBA_DATA_FILES
 GROUP BY TABLESPACE_NAME) D,
 (SELECT TABLESPACE_NAME,
 ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
 FROM DBA_FREE_SPACE
 GROUP BY TABLESPACE_NAME) F
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
 UNION ALL
 SELECT D.TABLESPACE_NAME,
 SPACE || 'M' "SUM_SPACE(M)",
 BLOCKS SUM_BLOCKS,
 USED_SPACE || 'M' "USED_SPACE(M)",
 ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
 NVL(FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"
 FROM (SELECT TABLESPACE_NAME,
 ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
 SUM(BLOCKS) BLOCKS
 FROM DBA_TEMP_FILES
 GROUP BY TABLESPACE_NAME) D,
 (SELECT TABLESPACE_NAME,
 ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
 ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
 FROM V$TEMP_SPACE_HEADER
 GROUP BY TABLESPACE_NAME) F
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
 ORDER BY 1)
 WHERE TABLESPACE_NAME IN ('SYSAUX', 'SYSTEM');

欢迎大家来到IT世界,在知识的湖畔探索吧!

可见，SYSAUX表空间已经使用了43GB左右，SYSTEM表空间已经使用了3GB左右。

2、查看下使用SYSTEM和SYSAUX表空间的比较大的表

欢迎大家来到IT世界,在知识的湖畔探索吧!select *
 from (select segment_name,PARTITION_NAME,
 sum(bytes) / 1024 / 1024 total_mb,
 tablespace_name
 from dba_segments
 where tablespace_name in ('SYSTEM', 'SYSAUX')
 group by segment_name, tablespace_name
 order by 3 desc)
 where rownum <= 20;

可见，大表大部分都是AUD$和WRH$开头的AWR基表，AUD$使用SYSTEM表空间，AWR的基表使用SYSAUX表空间

3、查看SYSAUX表空间的具体使用情况

这里可以通过v$sysaux_occupants视图查询到

SELECT occupant_name "Item",
 space_usage_kbytes / 1048576 "Space Used (GB)",
 schema_name "Schema",
 move_procedure "Move Procedure"
 FROM v$sysaux_occupants
 ORDER BY space_usage_kbytes desc;

可见SM/AWR组件就使用了40GB的SYSAUX表空间，也就是说审计和AWR占用了大量的SYSTEM和SYSAUX表空间，而这些数据是可以定期清理的，都没有必要保留太长的时间。

通过查看v$sysaux_occupants视图，可以确定占用SYSAUX表空间过多的大部分都是AWR的基表，这样只要删除部分AWR数据理论上就可以回收一部分SYSAUX表空间，通常AWR的数据都会设置保留期限，10g版本默认保留7天，11g版本默认保留8天，可以通过dba_hist_wr_control视图来查看(注：并不是所有DBA开头的表都是数据字典，也有很多是视图，dba_hist_wr_control就是视图)

问题来了，AWR的数据既然只保留七八天，为什么还会占用这么多的SYSAUX表空间呢？这个问题其实有以下两个原因，首先，AWR删除过期的数据是通过DELETE操作完成的，这样就会产生大量的碎片，特别是SYSAUX表空间存在自动扩展的数据文件，而且这个数据文件没有扩展到最大，还有扩展的空间情况下会很明显，其次就是ASH的数据有些情况下是不受AWR的保留策略影响的。

4、使用dbms_workload_repository.drop_snapshot_range删除历史数据（不推荐）

欢迎大家来到IT世界,在知识的湖畔探索吧!--ASH的数据从第一个快照开始一直都在保留，导致WRH$_ACTIVE_SESSION_HISTORY表很大，使用DBMS_WORKLOAD_REPOSITORY包
--清理过期或者不需要的AWR数据，可以回收这部分空间
select count(*) from WRH$_ACTIVE_SESSION_HISTORY;
select min(snap_id),max(snap_id) from wrh$_active_session_history;
exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id =>1,high_snap_id => 25100);
select count(*) from WRH$_ACTIVE_SESSION_HISTORY;

清理了AWR数据之后，可以发现SYSAUX表空间的空间并没有被回收，使用率还和之前一样，这是因为清理AWR操作是通过DELETE操作实现的，表的水位线并没有下降导致的。

5、手工生成truncate，需要在SYS下执行

select distinct 'truncate table ' || segment_name || ';',
 s.bytes/1024/1024 MB
 from dba_segments s
 where s.segment_name like 'WRH$%'
 and segment_type in ('TABLE PARTITION', 'TABLE')
 and s.bytes/1024/1024 >100
 order by s.bytes/1024/1024 desc;

实际执行sql:通过上面语句可以看到基本上都是以WRH$_开头的段，这些类型的段基本上都是与AWR相关的,以下均以sys用户执行

truncate table WRH$_SQL_BIND_METADATA; —-保存AWR收集SQL绑定信息表
truncate table WRH$_ACTIVE_SESSION_HISTORY; ——保存AWR收集历史会话信息表
select segment_name,partition_name,bytes/1024/1024/1024 gb from dba_segments where segment_name in('WRH$_LATCH','WRH$_SQLSTAT');
alter table WRH$_LATCH truncate partition WRH$_LATCH_1013373590_0;
alter table WRH$_SQLSTAT truncate partition WRH$_SQLSTA_1013373590_0;
truncate table WRI$_ADV_SQLT_PLANS; —保存AWR收集SQL建议计划信息表
alter table WRH$_SYSSTAT shrink space;
alter index WRH$_SYSSTAT_PK shrink space;
alter table WRH$_LATCH shrink space;
alter table WRH$_SEG_STAT shrink space;
alter table WRH$_SQLSTAT shrink space;
alter table WRH$_PARAMETER shrink space; —-保存AWR收集参数信息表
alter index WRH$_PARAMETER_PK shrink space; 
truncate table WRH$_EVENT_HISTOGRAM;
truncate table WRH$_SQL_PLAN; —-保存AWR收集的SQL执行计划表
truncate table WRH$_SQLTEXT; –—保存AWR收集的SQL文本表

6、验证

达到需求，完。

觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~