分享一份Oracle数据库标准的SYSAUX表空间清理方案

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

概述

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

[[283613]]

下面介绍下最近清理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'); 
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.
  • 34.
  • 35.
  • 36.
  • 37.

分享一份Oracle数据库标准的SYSAUX表空间清理方案

可见,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; 
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.

分享一份Oracle数据库标准的SYSAUX表空间清理方案

可见,大表大部分都是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; 
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.

分享一份Oracle数据库标准的SYSAUX表空间清理方案

可见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就是视图)

分享一份Oracle数据库标准的SYSAUX表空间清理方案

问题来了,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; 
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.

清理了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; 
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.

分享一份Oracle数据库标准的SYSAUX表空间清理方案

实际执行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文本表 
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.

分享一份Oracle数据库标准的SYSAUX表空间清理方案

6. 验证

达到需求,完。

责任编辑:赵宁宁 来源: 今日头条
相关推荐

2019-07-17 07:07:54

MySQL数据库索引

2019-08-12 08:47:14

脚本语言数据库MySQL

2019-12-10 08:06:16

数据库1NF数据库范式

2019-10-12 00:39:23

MySQL数据库Oracle

2019-09-23 11:29:21

mysql数据库开发

2010-05-10 11:34:38

Oracle系统表空间

2010-03-24 09:42:12

Oracle数据库

2011-08-05 12:44:57

Oracle数据库表空间

2015-03-19 15:17:11

2010-04-02 18:01:39

Oracle清理

2023-09-29 22:41:26

Kubernetes云原生

2011-03-25 10:26:34

Oracle数据库管理表空间

2009-05-14 13:36:11

Oracle表空间优化

2009-07-02 00:26:00

临时表空间Oracle数据库性能

2011-06-03 16:30:39

2011-08-23 11:09:36

Oraclerman恢复system表空间恢复

2020-12-10 09:41:15

ZabbixOracle数据库

2010-04-23 11:32:22

Oracle数据库

2010-04-22 17:06:24

Oracle数据库

2011-08-29 17:00:47

Oracle数据库RM表空间数据块介质
点赞
收藏

51CTO技术栈公众号