oracle 10g的sysaux空间暴增与空间回收，在oracle10中表空间sysaux引入，oracle把统计信息存储在这里，这也是为了更好的优化syst
oracle10g的sysaux空间暴增与空间回收
在oracle10中表空间sysaux引入，，oracle把统计信息存储在这里，这也是为了更好的优化system表空间，我们可以用视图v$sysaux_occupants 查看，oracle有哪些数据存贮在sysaux中。
select occupant_name, space_usage_kbytes from v$sysaux_occupants;
oracle的sm/awr, sm/advisor, sm/optstat and sm/other的统计信息都存储在sysaux中，这里重点介绍sm/optstat。
sm/optstat：用于存储老版本的优化统计信息，在oracle10g中，在我们手动或自动更新统计信息使oracle选择了错误的执行计划。oracle10g是可以恢复旧版本的统计信息，这个统计信息默认保存31天
查询当前sm/optstat的统计信息的保存时间
sql> select dbms_stats.get_stats_history_retention from dual;
get_stats_history_retention
---------------------------
31
修改sm/optstat的统计信息的保存时间为10天
sql> exec dbms_stats.alter_stats_history_retention(10);
pl/sql procedure successfully completed
sql> select dbms_stats.get_stats_history_retention from dual;
get_stats_history_retention
---------------------------
10
sql>
删除16天前的统计数据
sql> exec dbms_stats.purge_stats(sysdate-16);
pl/sql procedure successfully completed
sql>
查看当前有效的统计数据是到什么时间的
sql> select dbms_stats.get_stats_history_availability from dual;
get_stats_history_availability
--------------------------------------------------------------------------------
12-2月 -12 07.15.49.000000000 下午 +08:00
再删除7天前的统计数据
sql> exec dbms_stats.purge_stats(sysdate-7);
pl/sql procedure successfully completed
这个时候发现有效的统计信息时间已经变了
sql> select dbms_stats.get_stats_history_availability from dual;
get_stats_history_availability
--------------------------------------------------------------------------------
14-2月 -12 07.15.57.000000000 下午 +08:00
sql>
这个时候虽然删除了数据，但空间还没有回收，如何回收空间呢？
没有释放空间是因为“purge_stats”用delete的方式删除数据，虽然数据没了，但是hwm还没降下来，查看optstat使用哪些表，然后降低其高水位即可。
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$
7 rows selected
sql>
再结合如下sql判断哪个表大，然后就move哪个表
sql> select a.table_name,a.num_rows from dba_tables a where a.tablespace_name='sysaux' and a.table_name like '%optstat%'
2 ;
table_name num_rows
------------------------------ ----------
wri$_optstat_opr 151
wri$_optstat_aux_history 0
wri$_optstat_histgrm_history 139933
wri$_optstat_histhead_history 14406
wri$_optstat_ind_history 1196
wri$_optstat_tab_history 1323
6 rows selected
sql>
再用如下语句查出相关表的索引，因为move表，索引会失效，需要重建索引
sql> select i.index_name,i.table_name,i.status,i.table_owner
2 from dba_indexes i,dba_objects s where i.table_name=s.object_name and s.object_name like '%optstat%' and s.object_type='table'
3 ;
index_name table_name status table_owner
------------------------------ ------------------------------ -------- ------------------------------
i_wri$_optstat_tab_obj#_st wri$_optstat_tab_history valid sys
i_wri$_optstat_tab_st wri$_optstat_tab_history valid sys
i_wri$_optstat_ind_obj#_st wri$_optstat_ind_history valid sys
i_wri$_optstat_ind_st wri$_optstat_ind_history valid sys
i_wri$_optstat_hh_obj_icol_st wri$_optstat_histhead_history valid sys
i_wri$_optstat_hh_st wri$_optstat_histhead_history valid sys
i_wri$_optstat_h_obj#_icol#_st wri$_optstat_histgrm_history valid sys
i_wri$_optstat_h_st wri$_optstat_histgrm_history valid sys
i_wri$_optstat_aux_st wri$_optstat_aux_history valid sys
i_wri$_optstat_opr_stime wri$_optstat_opr valid sys
10 rows selected
sql>