Oracle数据库标准的SYSAUX表空间清理方法是什么

60次阅读

共计 4451 个字符，预计需要花费 12 分钟才能阅读完成。

这篇文章将为大家详细讲解有关 Oracle 数据库标准的 SYSAUX 表空间清理方法是什么，文章内容质量较高，因此丸趣 TV 小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。

概述

SYSAUX 表空间被称为系统辅助表空间，是 10g 版本开始推出的新功能，主要的目的是为 SYSTEM 表空间减负，Oracle 对 SYSTEM 表空间的维护有一套独立的体系，对 SYSTEM 表空间操作会占用额外的 CPU 资源，而且效率低下。在 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

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

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

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 删除历史数据 (不推荐)

--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;  mdash;- 保存 AWR 收集 SQL 绑定信息表  truncate table WRH$_ACTIVE_SESSION_HISTORY;  mdash; mdash; 保存 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;  mdash; 保存 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;  mdash;- 保存 AWR 收集参数信息表  alter index WRH$_PARAMETER_PK shrink space; truncate table WRH$_EVENT_HISTOGRAM; truncate table WRH$_SQL_PLAN;  mdash;- 保存 AWR 收集的 SQL 执行计划表  truncate table WRH$_SQLTEXT;  ndash; mdash; 保存 AWR 收集的 SQL 文本表

6. 验证

达到需求，完。

关于 Oracle 数据库标准的 SYSAUX 表空间清理方法是什么就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。

正文完