sysaux 表空间基于sm/optstat组件爆满—m6米乐安卓版下载的解决方案汇总 -m6米乐安卓版下载

背景

  经历了一次“oracle sysaux表空间异常爆满”，当时查资料几种解决思路，在此做个汇总方便后续查看。不严谨的地方欢迎大家指正

问题介绍

• sysaux表空间中占用空间的组件

set line 800
col occupant_name for a30
col occupant_desc for a60
select occupant_name,occupant_desc,space_usage_kbytes/1024 usage_mb
from v$sysaux_occupants order by space_usage_kbytes desc;
occupant_name                  occupant_desc                                                  usage_mb
------------------------------ ------------------------------------------------------------ ----------
sm/optstat -->优化器统计信息   server manageability - optimizer statistics history            23922.25

• sysaux 表空间

表空间名                       表空间大小(m) 已使用空间(m) 使用比   空闲空间(m)  最大块(m)
------------------------------ ------------- ------------- -------- ----------- ----------
sysaux                                 32720      32673.31   99.86%       46.69    

由此可以看到sm/optstat组件占比 sysaux 空间：73%

sm/optstat简介

sm/optstat是用于存储老的统计信息。
（1）10g之前，当对表/字段/索引做了相应的统计信息之后，新的统计信息就会覆盖老的统计信息，也就是说的无法直接找回统计信息，要找回，只能事先通过dbms_stats导出来。
（2）10g之后，就不必了，它会自动的存到相应的表里，而这些表是存在sysaux的，但这也引出了一个问题：如果这些表的数据不断的增长，而不把老的数据删除的话，sysaux迟早会被撑爆。
默认的情况下,系统会为sm/optstat保留31天的记录，可以通过dbms_stats.get_stats_history_retention 来确定。

sql> select dbms_stats.get_stats_history_retention from dual;
get_stats_history_retention
---------------------------
                         31

sm/optstat保留的时间可以通过dbms_stats.alter_stats_history_retention来控制。

-- 将历史统计信息保留时间设为无限:
sql> exec dbms_stats.alter_stats_history_retention(-1);
-- 将历史统计信息保留时间设为7天
sql> exec dbms_stats.alter_stats_history_retention(7);
-- 查看历史统计信息保留时间
sql> select dbms_stats.get_stats_history_availability from dual;
get_stats_history_availability
---------------------------------------------------------------------------
04-nov-22 10.00.07.354209000 pm  08:00

如果sm/optstat确实占用了比较多的空间，要删除某个时间前的记录可以用：dbms_stats.purge_stats

-- 清理3天前的历史统计信息
sql>  exec dbms_stats.purge_stats(sysdate-3);
pl/sql procedure successfully completed.

这个sp其实只从存储历史统计信息的表里删除记录，这样的话，就会出现一种情况，删除了大量的数据，但这些表占用的空间并没有释放,也就是hwm不会降下来的。这时要手工处理

处理方案

方案一：来自(文档 id 1055547.1)的方案：

• 涉及的表
  
• 原因
  
• 解决
  
  

方案二：直接truncate 表

• 1、将历史统计信息保留时间设为无限:

exec dbms_stats.alter_stats_history_retention(-1);

• 2、truncate table:

truncate table sys.wri$_optstat_histhead_history; 
truncate table sys.wri$_optstat_histgrm_history;

• 3、清理历史统计信息(清理7天前的信息)

exec dbms_stats.purge_stats(sysdate-7); 

• 4、将历史统计信息保留时间设为7天（默认31天）

exec dbms_stats.alter_stats_history_retention(7);

• 5、避免有其它问题，执行一次数据库的收集任务：

sql> exec dbms_stats.gather_database_stats_job_proc();
pl/sql procedure successfully completed.

方案三：move table 达到清理碎片的目地

• 将表move 到users表空间

sql> alter table wri$_optstat_histhead_history move tablespace users;
-- lob 字段字段需要单独move 
sql> alter table wri$_optstat_histhead_history move tablespace users lob (expression) store as lobsegment (tablespace users);
sql> alter index i_wri$_optstat_hh_obj_icol_st  rebuild online;
sql> alter index i_wri$_optstat_hh_st rebuild online;

• 重新将表move 回原空间：sysaux

sql> alter table wri$_optstat_histhead_history move tablespace sysaux;
sql> alter table wri$_optstat_histhead_history move tablespace sysaux lob (expression) store as lobsegment (tablespace sysaux);
sql> alter index i_wri$_optstat_hh_obj_icol_st  rebuild online;
sql> alter index i_wri$_optstat_hh_st rebuild online;

方案四：重创建表

• 根据create table as 创建一张指定范围内的表备做备份，truncate 表后再重新导入

sql> create table wri$_optstat_histgrm_history_b as select * from wri$_optstat_histgrm_history where to_char(savtime,'yyyy-mm-dd')>'2022-11-09';
sql> truncate table  wri$_optstat_histgrm_history ;
sql> insert into wri$_optstat_histgrm_history  select * from  wri$_optstat_histgrm_history_b;
sql> commit;
sql> drop table wri$_optstat_histgrm_history_b purge;

总结：

• 不一定是全的，但应该有一款适合你！