1. 问题现象

发现 SYSAUX 表空间使用率过高如下：http://www.cndba.cn/dave/article/131625http://www.cndba.cn/dave/article/131625

[root@www.cndba.cn ~]# orz tsfree
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
| TABLESPACE_NAME | TOTAL_SIZE(Mb) | FREE_SIZE(Mb) | USED_SIZE(Mb) | USED_RATE(%) |
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
……
| SYSAUX          | 54074          | 2404.94       | 51669.06      | 95.55%       |
| TEMP            | 32767          | 13185         | 19582         | 59.76%       |
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

查看使用过高的对象：

[root@www.cndba.cn ~]# su - oracle
[oracle@www.cndba.cn ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 14 09:26:04 2025

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> set lines 140
SQL> col segment_name for a40
SQL> set pages 200
SQL> 
SQL> select * from (
  2    select segment_name,sum(bytes)/1024/1024 total_mb,tablespace_name from dba_segments where tablespace_name in 
  3  ('SYSTEM','SYSAUX') group by segment_name,tablespace_name order by 2 desc)
  4    where rownum <=20;

SEGMENT_NAME                   TOTAL_MB TABLESPACE_NAME
---------------------------------------- ---------- ------------------------------
WRH$_ACTIVE_SESSION_HISTORY         31301.0625 SYSAUX
WRH$_ACTIVE_SESSION_HISTORY_PK          5012.0625 SYSAUX
WRH$_EVENT_HISTOGRAM_PK           2247.0625 SYSAUX
WRH$_EVENT_HISTOGRAM              1602.0625 SYSAUX
WRH$_LATCH_MISSES_SUMMARY_PK           949.0625 SYSAUX
WRH$_SYSSTAT_PK                787.0625 SYSAUX
WRH$_LATCH                   749.0625 SYSAUX
WRH$_SQLSTAT                   723.0625 SYSAUX
WRH$_LATCH_MISSES_SUMMARY           697.0625 SYSAUX
WRH$_LATCH_PK                   613.0625 SYSAUX
WRH$_PARAMETER_PK               550.0625 SYSAUX
WRH$_PARAMETER                   496.0625 SYSAUX
WRH$_SYSSTAT                   478.0625 SYSAUX
WRH$_SEG_STAT                   404.0625 SYSAUX
WRH$_SYSTEM_EVENT               369.0625 SYSAUX
WRH$_SYSTEM_EVENT_PK               314.0625 SYSAUX
IDL_UB1$                    279 SYSTEM
WRH$_SERVICE_STAT_PK               256.0625 SYSAUX
WRH$_SQL_PLAN                    208 SYSAUX
WRH$_SEG_STAT_PK               192.0625 SYSAUX

20 rows selected.

SQL>

结果是WRH$_ACTIVE_SESSION_HISTORY ，查看分区情况：http://www.cndba.cn/dave/article/131625

SQL>  SELECT owner,
  2    segment_name,
  3    partition_name,
  4    segment_type,
  5    bytes/1024/1024/1024 Size_GB
  6  FROM dba_segments
  7  WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY';

OWNER                   SEGMENT_NAME                PARTITION_NAME               SEGMENT_TYPE         SIZE_GB
------------------------------ ---------------------------------------- ------------------------------ ------------------ ----------
SYS                   WRH$_ACTIVE_SESSION_HISTORY        WRH$_ACTIVE_1680763292_102194  TABLE PARTITION      30.5673828
SYS                   WRH$_ACTIVE_SESSION_HISTORY        WRH$_ACTIVE_SES_MXDB_MXSN      TABLE PARTITION      .000061035

2. 处理方法

官网解释的原因如下：http://www.cndba.cn/dave/article/131625

WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy (Doc ID 387914.1)http://www.cndba.cn/dave/article/131625

http://www.cndba.cn/dave/article/131625

Oracle decides what rows need to be purged based on the retention policy. There is a special mechanism which is used in the case of the large AWR tables where we store the snapshot data in partitions. One method of purging data from these tables is by removing partitions that only contain rows that have exceeded the retention criteria. During the nightly purge task, we only drop the partition if all the data in the partition has expired. If the partition contains at least one row which, according to the retention policy shouldn’t be removed, then the partition won’t be dropped and as such the table will contain old data.http://www.cndba.cn/dave/article/131625

If partition splits do not occur (for whatever reason), then we can end up with a situation where we have to wait for the latest entries to expire before the partition that they sit in can be removed. This can mean that some of the older entries can be retained significantly past their expiry date. The result of this is that the data is not purged as expected.

http://www.cndba.cn/dave/article/131625

手工分区：http://www.cndba.cn/dave/article/131625

SQL> alter session set "_swrf_test_action" = 72;

Session altered.

SQL> SELECT owner,
  2    segment_name,
  3    partition_name,
  4    segment_type,
  5    bytes/1024/1024/1024 Size_GB
  6  FROM dba_segments
  7  WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY';

OWNER                   SEGMENT_NAME                PARTITION_NAME               SEGMENT_TYPE         SIZE_GB
------------------------------ ---------------------------------------- ------------------------------ ------------------ ----------
SYS                   WRH$_ACTIVE_SESSION_HISTORY        WRH$_ACTIVE_1680763292_102194  TABLE PARTITION      30.5673828
SYS                   WRH$_ACTIVE_SESSION_HISTORY        WRH$_ACTIVE_1680763292_113699  TABLE PARTITION      .000061035
SYS                   WRH$_ACTIVE_SESSION_HISTORY        WRH$_ACTIVE_SES_MXDB_MXSN      TABLE PARTITION      .000061035

增加新分区,过一段时间之后,自动清理WRH$_ACTIVE_SESSION_HISTORY表历史数据. 如果想即时降下来，也可以直接truncate 分区：http://www.cndba.cn/dave/article/131625

alter table sys.wrh$_active_session_history truncate partition WRH$_ACTIVE_1680763292_102194 update global indexes;

版权声明：本文为博主原创文章，未经博主允许不得转载。