运维日记|sysaux表空间清理

新运维新数据 2021-01-16

1630

各位新朋友～记得先点蓝字关注我哦～

Sysaux表空间是system表空间的辅助表空间，他主要存放awr快照，审计信息等信息，默认情况下，sysaux表空间在日常使用的过程中会越来越大使用量，那么怎么才能安全有效的清理sysaux表空间呢？

First 检查表空间使用情况

Select Tablespace_Name,
       Sum_m,
       Max_m,
       Count_Blocks Free_Blk_Cnt,
       Sum_Free_m,
       To_Char(100 * Sum_Free_m  Sum_m, '99.9999') || '%' As Pct_Free,
       100 - To_Char(100 * Sum_Free_m  Sum_m, '99.9999') || '%' As Pct_used
  From (Select Tablespace_Name, Sum(Bytes)  1024  1024 As Sum_m
          From Dba_Data_Files
         Group By Tablespace_Name)
  Left Join (Select Tablespace_Name As Fs_Ts_Name,
                    Max(Bytes)  1024  1024 As Max_m,
                    Count(Blocks) As Count_Blocks,
                    Sum(Bytes  1024  1024) As Sum_Free_m
               From Dba_Free_Space
              Group By Tablespace_Name)
    On Tablespace_Name = Fs_Ts_Name
 ORDER BY Sum_Free_m  Sum_m;

可以看到sysaux使用空间有4G左右，再去查询下sysaux表空间里具体存放了什么数据

SQL> col Item for a30
SQL> col Schema for a20
SQL> set lines 200
SQL> SELECT occupant_name"Item",
 round(space_usage_kbytes/1024/1024,3)"Space Used (GB)",
 schema_name "Schema",
 move_procedure "MoveProcedure"
 FROM v$sysaux_occupants
 ORDER BY 2 Desc;

主要就是AWR的数据占用,长期没有清理的话,就有可能存在暴增的情况,那么怎么清理空间呢？有两种方式

方式一:

HOME

适用于数据量不是很大的情况下,也是oracle官方给出的清理方案,利用存储过程进行清理(这里有一个问题就是该存储过程其实执行的是delete语句,在存储过程执行过程中,会生成大量的归档,需评估以后慎用)

1、首先查出snap_id

2、执行存储过程清除

EXECUTE DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(6838, 7004);

方式二:

HOME

trucate基表

先查出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$表发现这些基表中有相同的字段snap_id,我们就利用这个字段进行处理，先利用snap_id进行大基表的备份。

创建备份表

CREATE TABLE WRH$_ACTIVE_SESSION_HISTORY_B AS 
SELECT * FROM WRH$_ACTIVE_SESSION_HISTORY WHERE SNAP_ID>7000 ;

验证备份表信息

SELECT COUNT(*) FROM WRH$_ACTIVE_SESSION_HISTORY_B;

删除大的基表

TRUNCATE TABLE WRH$_ACTIVE_SESSION_HISTORY;

将备份数据恢复到源表

INSERT INTO WRH$_ACTIVE_SESSION_HISTORY SELECT * FROM WRH$_ACTIVE_SESSION_HISTORY_B;
COMMIT;

然后验证下数据是否写入到了源表中

最后删除备份临时表

再次查询发现基表信息已经清理成功！

空间一直是数据库老生常谈的问题之一，保证空间合理利用才能让数据库更好更高效地运行~

美创运维中心数据库服务团队拥有Oracle ACE 1人、OCM 10余人、数十名Oracle OCP、MySQL OCP、红帽RHCA、中间件weblogic、tuxedo认证、达梦工程师，著有《Oracle DBA实战攻略》，《Oracle数据库性能优化方法和最佳实践》，《Oracle内核技术揭秘》等多本数据运维优化书籍。目前运维各类数据库合计2000余套，精通Oracle、MySQL、SQLServer、DB2、PostgreSQL、达梦等主流商业和开源数据库。并成为首批国内达梦战略合作伙伴之一，拥有海量经验和完善的人员培养体系。并同时提供超融合，私有云整体解决方案。

数据库

文章转载自新运维新数据，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

运维日记|sysaux表空间清理

评论