Oracle 10g的sysaux空间暴增与空间回收

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>

Oracle中创建自增字段方法ora-00600: internal error code, arguments: [KGHLKREM1], [0x838000020], [], [], [], [], [], [], [], [相关资讯 oracle数据库教程

Oracle raw数据类型介绍（01/29/2013 10:05:53）
监听器注册与ORA-12514 错误分析（11/13/2012 14:30:08）
Oracle SQL的cursor理解（11/13/2012 14:16:17）

Oracle 如何强制刷新Buffer Cache （01/29/2013 10:02:46）
dblink致Oracle库的SCN变成两库的（11/13/2012 14:24:41）
Linux操作系统下完全删除Oracle数（11/13/2012 08:25:52）

本文评论查看全部评论（0）