sysaux快满了

最新推荐文章于 2025-06-24 17:50:03 发布

原创最新推荐文章于 2025-06-24 17:50:03 发布 · 3.5k 阅读

CC 4.0 BY-SA版权

文章标签：

#session #sql #table #system #redhat #2010

手工生成快照又报错：
SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();sysaux快满了（一共3G），其中AWR的信息占了1.5G左右

alert 中：
Wed Sep 08 09:00:47 2010
ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_1153364305_5522 by 4096 in                tablespace SYSAUX
MMON Flush encountered SYSAUX out of space error(1688).
MMON (emergency) purge of WR snapshots (15357) and older
ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_1153364305_5522 by 4096 in                tablespace SYSAUX
MMON Flush encountered SYSAUX out of space error(1688).
MMON (emergency) purge of WR snapshots (15358) and older
Wed Sep 08 09:01:05 2010
ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_1153364305_5522 by 4096 in                tablespace SYSAUX
$
所以想删除一部快照
但奇怪的是
SQL> select count(*) from dba_hist_snapshot;
  COUNT(*)
----------
      0

BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(); END;
*
ERROR at line 1:
ORA-13509: error encountered during updates to a AWR table
ORA-01688: unable to extend table ORA-01688: unable to extend table
SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_1153364305_5522 by 4096
in tablespace SYSAUX
. partition  by  in tablespace
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 14
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 37
ORA-06512: at line 1

我又想把保留时间减小点
SQL> select * from dba_hist_wr_control;
   DBID
----------
SNAP_INTERVAL
---------------------------------------------------------------------------
RETENTION
---------------------------------------------------------------------------
TOPNSQL
----------
1153364305
+00000 01:00:00.0
+00008 00:00:00.0
DEFAULT

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>60,retention=>3*24*60);
BEGIN dbms_workload_repository.modify_snapshot_settings(interval=>60,retention=>3*24*60); END;
*
ERROR at line 1:
ORA-13541: system moving window baseline size (691200) greater than retention
(259200)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 89
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 137
ORA-06512: at line 1
SQL>exec dbms_workload_repository.modify_snapshot_settings(interval=>60,retention=>3*24*60);
BEGIN dbms_workload_repository.modify_snapshot_settings(interval=>60,retention=>3*24*60); END;
*
ERROR at line 1:
ORA-13541: system moving window baseline size (691200) greater than retention
(259200)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 89
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 137
ORA-06512: at line 1

在网上查了查，有篇文章说是跟baseline设置有关，可我这边还是没有记录
SQL> select count(*) from dba_hist_baseline;
  COUNT(*)
----------
      0
SQL>

查这个V$SYSAUX_OCCUPANTS半天没有返回值，一会我查下被谁给阻塞了
之前用dba_segments查的

OWNER                SEGMENT_NAME                               SEGMENT_TYPE                   BYTES/1024/1024

SYS                      WRH$_SYSMETRIC_HISTORY_INDEX    index                               172

SYS                      WRH$_SYSMETRIC_HISTORY             TABLE                               237

SYS                      WRH$_ACTIVE_SESSION_HISTORY    TABLE PARTITION             1030

我现在是把AWR自动收集给停了，ALERT里面不报那个1688错误了

但我开始怀疑AWR的数据到底存放在哪里
SQL> select count(*) from WRH$_ACTIVE_SESSION_HISTORY;

  COUNT(*)
----------
      0

SQL> select bytes/1024/1024 from dba_segments where segment_name='WRH$_ACTIVE_SESSION_HISTORY';

BYTES/1024/1024
---------------
         1030
         .125

SQL>

这难道不奇怪吗，还有v$sysaux_occupants这个视图一带上 SPACE_USAGE_KBYTES 列的SELECT就挂在那不动。

我在自己的本上建了一个新库，倒是可以查了，找到 SM/AWR 对应的大小，一开始是16M，我手工建快照，这个值慢慢增加到20M，这时已经建了37个snapshot
但这个时候查 wrh$_active_session_history，大小一直没变，是64K。我测试环境是 vm redhat 10.2.0.1, 真是弄不懂。

WRH$_ACTIVE_SESSION_HISTORY有个日期字段，根据retention看下是不是有超出retention范围的数据没有purge掉
要是没purge掉可能就是bug了，或者purge的时候没有成功，或者报错

空间满了看上去就会不正常
今天上午把一些WRH$的表truncate了（没有新加文件）
空间释放了，问题就基本解决了
可以生成新的快照了
我就手工生成了两个快照
SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

PL/SQL procedure successfully completed.

SQL> select count(*) from sys.wrh$_active_session_history;

  COUNT(*)
----------
   18504

SQL> select count(*) from dba_hist_snapshot;

  COUNT(*)
----------
      2
问了个朋友，他是经常清理WRH$这些表的，看来要保持SYSAUX一定的free空间。
现在还有个遗留问题，v$sysaux_occupants视图，一SELECT  space那列就被挂在那，先放放了，还有别的报告要写...