sysaux表空间空间不足处理方法

1、查看表空间使用率

select *
  from (select  'check_tablespaces',a.tablespace_name,
               (round(((a.b - b.b) / a.mb) * 100, 1)) max_free_PERCENT
          from (select tablespace_name,
                       round(sum(bytes) / (1024 * 1024)) b,
                       round(sum(decode(maxbytes, 0, bytes, maxbytes)) / (1024 * 1024)) mb
                  from dba_data_files
                 group by tablespace_name) a,
               (select tablespace_name, round(sum(bytes) / (1024 * 1024)) b
                  from dba_free_space
                 group by tablespace_name) b
         where a.tablespace_name = b.tablespace_name) aa
		  where aa.max_free_PERCENT > '百分比';

2、查看sysaux表空间对应的数据文件和大小

set line 300
col name format a90
select a.file#,a.name,b.TABLESPACE_NAME,(b.bytes/1024/1024/1024) as file_size from v$datafile a,dba_data_files b where a.file#=b.file_id and b.tablespace_name='SYSAUX' order by a.file#;

3、查看表空间SYSAUX占用段最高的对象

SELECT * FROM (SELECT SEGMENT_NAME,
               PARTITION_NAME,
               SEGMENT_TYPE,
               BYTES / 1024 / 1024 /1024  SIZE_G
          FROM DBA_SEGMENTS
         WHERE TABLESPACE_NAME = 'SYSAUX'
         ORDER BY 4 DESC)
 WHERE ROWNUM <= 10;

4、查看占用SYSAUX空间最大的组件

SELECT OCCUPANT_NAME "Item",
       OCCUPANT_DESC,
       SPACE_USAGE_KBYTES / 1024 / 1024 "Space Used (GB)",
       SCHEMA_NAME "Schema",
       MOVE_PROCEDURE "Move Procedure",
       MOVE_PROCEDURE_DESC
  FROM V$SYSAUX_OCCUPANTS
 ORDER BY "Space Used (GB)" DESC;

由上述结果可以看到awr占用空间很大，通过查看前几的段对象可以看到最占用空间的是表
WRH$_ACTIVE_SESSION_HISTORY。
5、查看近几天的活动会话信息

SELECT TO_CHAR(SAMPLE_TIME, 'yyyy-mm-dd'), COUNT(*)
  FROM DBA_HIST_ACTIVE_SESS_HISTORY
 WHERE sample_time>=to_date('2020-05-10','yyyy-mm-dd')
 GROUP BY TO_CHAR(SAMPLE_TIME, 'yyyy-mm-dd') 
 ORDER BY 1;

可以看到近几天的活动会话很多，每次活动会话都会往基表WRH$_ACTIVE_SESSION_HISTORY插入一条数据，所以这个基表的空间比较大。

6、处理表空间sysaux告警的方法

（1）增大表空间对应的数据文件大小
（2）如果服务器磁盘空间不足，如何清理呢？在测试环境测试的是可以truncate 这些性能基表的。

SELECT DISTINCT 'truncate  table  ' || SEGMENT_NAME || ';',
                S.BYTES / 1024 / 1024
  FROM DBA_SEGMENTS S
 WHERE S.SEGMENT_NAME LIKE 'WR%'
   AND SEGMENT_TYPE IN ('TABLE PARTITION', 'TABLE')
   AND S.BYTES / 1024 / 1024 > 100
 ORDER BY S.BYTES / 1024 / 1024 DESC;