统计服务器AYSAUX使用空间过多的问题

来源：互联网发布：算法流程图模板编辑：程序博客网时间：2024/05/22 05:08

统计alert中报SYSAUX表空间不足，已经扩到1G的表空间为什么会还会不足，SYSAUX中纠结存放了什么内容，以前就知道oracle10g中awr信息是存放在sysaux中。

查了下资料，做了些知识补充：

sysaux表空间内容信息从如下视图可以查询到：

SQL> select OCCUPANT_NAME,SPACE_USAGE_KBYTES from v$sysaux_occupants a order by a.SPACE_USAGE_KBYTES desc;

OCCUPANT_NAME SPACE_USAGE_KBYTES

---------------------------------------------------------------- ------------------

SM/AWR 195072

SM/OPTSTAT 152128

SM/ADVISOR 13888

LOGMNR 6144

SM/OTHER 4992

EM_MONITORING_USER 1792

LOGSTDBY 896

AO 768

XSOQHIST 768

STREAMS 512

JOB_SCHEDULER 512

TSM 256

ODM 0

SDO 0

WM 0

ORDIM 0

ORDIM/PLUGINS 0

ORDIM/SQLMM 0

STATSPACK 0

TEXT 0

ULTRASEARCH 0

ULTRASEARCH_DEMO_USER 0

EXPRESSION_FILTER 0

XSAMD 0

XDB 0

EM 0

可以看到SM/AWR 、SM/OPTSTAT 占用了绝大部分

SM/AWR是快照信息，它的保存时间，实现机制不用多说了

SM/OPTSTAT用于存放老的统计信息，默认保存时间是31天，SM/OPTSTAT的具体系统表组成可见系统脚本【$ORACLE_HOME/rdbms/admin/catost.sql】

查询SM/OPTSTAT的保存时间：

SQL>select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION

---------------------------

修改保存时间为10天：

SQL>exec dbms_stats.alter_stats_history_retention(10);

删除十天前的

exec dbms_stats.purge_stats(sysdate-10);

这个删除实际上只是用delete的方式删除数据，虽然数据没了，但是HWM还没降下来，查看OPTSTAT使用哪些表，然后降低其高水位即可。

查询到相关的表，做一下shrink就可以看到空间下降了:

SQL> SELECT s.object_name from dba_objects s where s.object_name like '%OPTSTAT%' and s.object_type='TABLE'
2 ;
OBJECT_NAME
--------------------------------------------------------------------------------
WRI$_OPTSTAT_TAB_HISTORY
WRI$_OPTSTAT_IND_HISTORY
WRI$_OPTSTAT_HISTHEAD_HISTORY
WRI$_OPTSTAT_HISTGRM_HISTORY
WRI$_OPTSTAT_AUX_HISTORY
WRI$_OPTSTAT_OPR
OPTSTAT_HIST_CONTROL$

sql> alter table WRI$_OPTSTAT_TAB_HISTORY move;
sql> alter table WRI$_OPTSTAT_OPR move;
sql> alter table WRI$_OPTSTAT_IND_HISTORY move;
sql> alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move;
sql> alter table WRI$_OPTSTAT_HISTGRM_HISTORY move;
sql> alter table WRI$_OPTSTAT_AUX_HISTORY move;