oracle 空间清理工具,Oracle 清理sysaux表空间

查看sysaux表空间使用排行

select SEGMENT_NAME,BYTES/1024/1024/1024 DGD from dba_segments  where tablespace_name='SYSAUX' ORDER BY DGD DESC;

I_WRI$_OPTSTAT_H_ST

WRI$_OPTSTAT_HISTGRM_HISTORY

I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST

这问题是由最近一个数据库SYSAUX使用空间过多引出来的.

SM/OPTSTAT是用于存储老的统计信息，10G之前，当对表/字段/索引做了相应的统计信息之后，新的统计信息就会覆盖老的统计信息，也就是说的无法直接找回统计信息，要找回，只能事先通过dbms_stats导出来。10G就不必了，它会自动的存到相应的表里，而这些表是存在sysaux的，但这也引出了一个问题：如果这些表的数据不断的增长，而不把老的数据删除的话，sysaux迟早会暴了的。默认的情况下,系统会为SM/OPTSTAT保留31天的记录，可以通过dbms_stats.get_stats_history_retention来确定。这里的统计信息跟AWR是有区别的, AWR默认保留7天. SM/OPTSTAT保留的时间可以通过dbms_stats.alter_stats_history_retention来控制。如果SM/OPTSTAT确实占用了比较多的空间，要删除某个时间前的记录可以用：dbms_stats.purge_stats. 这个SP其实只从存储历史统计信息的表里删除记录，这样的话，就会出现一种情况，删除了大量的数据，但这些表占用的空间并没有释放,也就是HWM不会降下来的。这时要手工处理，顺便说一下用于存储SM/OPTSTAT相关信息的表是由脚$ORACLE_HOME/rdbms/admin/catost.sql来创建的.下面是测试：

SQL> show parameter sga_target

NAME TYPE VALUE

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

sga_target big integer 0

SQL> select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY

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

10-APR-10 11.15.35.339756000 PM -04:00

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION

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

31

SQL> select * from v$sysaux_occupants where occupant_name='SM/OPTSTAT';

OCCUPANT_NAME OCCUPANT_DESC SCHEMA_NAM MOVE_PROCE MOVE_PROCEDURE_DESC SPACE_USAGE_KBYTES

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

SM/OPTSTAT Server Manageability - Optimizer Statistics History SYS *** MOVE PROCEDURE NOT APPLICABLE *** 68416

SQL> select sum(SPACE_USAGE_KBYTES) from v$sysaux_occupants;

SUM(SPACE_USAGE_KBYTES)

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

175168

SQL> select owner,segment_name,sum(bytes)/1024/1024 from dba_extents where segment_name in (

2 'WRI$_OPTSTAT_TAB_HISTORY','I_WRI$_OPTSTAT_TAB_OBJ#_ST','I_WRI$_OPTSTAT_TAB_ST','WRI$_OPTSTAT_IND_HISTORY','I_WRI$_OPTSTAT_IND_OBJ#_ST'

,'I_WRI$_OPTSTAT_IND_ST','WRI$_OPTSTAT_HISTHEAD_HISTORY','I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST','I_WRI$_OPTSTAT_HH_ST','WRI$_OPTSTAT_HISTGRM_HISTORY'

3 4 ,'I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST','I_WRI$_OPTSTAT_H_ST','WRI$_OPTSTAT_AUX_HISTORY','I_WRI$_OPTSTAT_AUX_ST','WRI$_OPTSTAT_OPR'

5 ,'I_WRI$_OPTSTAT_OPR_STIME','OPTSTAT_HIST_CONTROL$')

6 group by owner,segment_name

7 order by 3;

OWNER SEGMENT_NAME SUM(BYTES)/1024/1024

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

SYS OPTSTAT_HIST_CONTROL$ .015625

SYS WRI$_OPTSTAT_AUX_HISTORY .0625

SYS I_WRI$_OPTSTAT_AUX_ST .0625

SYS WRI$_OPTSTAT_OPR .0625

SYS I_WRI$_OPTSTAT_OPR_STIME .0625

SYS I_WRI$_OPTSTAT_IND_ST .1875

SYS I_WRI$_OPTSTAT_TAB_ST .1875

SYS I_WRI$_OPTSTAT_IND_OBJ#_ST .25

SYS I_WRI$_OPTSTAT_TAB_OBJ#_ST .3125

SYS WRI$_OPTSTAT_TAB_HISTORY .3125

SYS WRI$_OPTSTAT_IND_HISTORY .3125

SYS I_WRI$_OPTSTAT_HH_ST 2

SYS I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST 3

SYS WRI$_OPTSTAT_HISTHEAD_HISTORY 4

SYS I_WRI$_OPTSTAT_H_ST 13

SYS WRI$_OPTSTAT_HISTGRM_HISTORY 17

SYS I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST 26

17 rows selected.

从这里可以看出，总共使用了175M sysaux,其中SM/OPTSTAT占用68M.

SQL> exec dbms_stats.purge_stats(sysdate-1);

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_stats_history_availability from dual;

11-MAY-10 10.22.53.000000000 PM -04:00

把之前的历史数据清空了.

SQL> select owner,segment_name,sum(bytes)/1024/1024 from dba_extents where segment_name in (

2 'WRI$_OPTSTAT_TAB_HISTORY','I_WRI$_OPTSTAT_TAB_OBJ#_ST','I_WRI$_OPTSTAT_TAB_ST','WRI$_OPTSTAT_IND_HISTORY','I_WRI$_OPTSTAT_IND_OBJ#_ST'

,'I_WRI$_OPTSTAT_IND_ST','WRI$_OPTSTAT_HISTHEAD_HISTORY','I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST','I_WRI$_OPTSTAT_HH_ST','WRI$_OPTSTAT_HISTGRM_HISTORY'

3 4 ,'I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST','I_WRI$_OPTSTAT_H_ST','WRI$_OPTSTAT_AUX_HISTORY','I_WRI$_OPTSTAT_AUX_ST','WRI$_OPTSTAT_OPR'

5 ,'I_WRI$_OPTSTAT_OPR_STIME','OPTSTAT_HIST_CONTROL$')

group by owner,segment_name

6 7 order by 3;

OWNER SEGMENT_NAME SUM(BYTES)/1024/1024

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

SYS OPTSTAT_HIST_CONTROL$ .015625

SYS WRI$_OPTSTAT_AUX_HISTORY .0625

SYS I_WRI$_OPTSTAT_AUX_ST .0625

SYS WRI$_OPTSTAT_OPR .0625

SYS I_WRI$_OPTSTAT_OPR_STIME .0625

SYS I_WRI$_OPTSTAT_IND_ST .1875

SYS I_WRI$_OPTSTAT_TAB_ST .1875

SYS I_WRI$_OPTSTAT_IND_OBJ#_ST .25

SYS I_WRI$_OPTSTAT_TAB_OBJ#_ST .3125

SYS WRI$_OPTSTAT_TAB_HISTORY .3125

SYS WRI$_OPTSTAT_IND_HISTORY .3125

SYS I_WRI$_OPTSTAT_HH_ST 2

SYS I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST 3

SYS WRI$_OPTSTAT_HISTHEAD_HISTORY 4

SYS I_WRI$_OPTSTAT_H_ST 13

SYS WRI$_OPTSTAT_HISTGRM_HISTORY 17

SYS I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST 26

17 rows selected.

SQL> select * from v$sysaux_occupants where occupant_name='SM/OPTSTAT';

OCCUPANT_NAME OCCUPANT_DESC SCHEMA_NAM MOVE_PROCE MOVE_PROCEDURE_DESC SPACE_USAGE_KBYTES

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

SM/OPTSTAT Server Manageability - Optimizer Statistics History SYS *** MOVE PROCEDURE NOT APPLICABLE *** 68416

SQL> select sum(SPACE_USAGE_KBYTES) from v$sysaux_occupants;

SUM(SPACE_USAGE_KBYTES)

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

175168

这里可以看出purge完后，这些空间并没有释放.

SQL> show user

USER is "SYS"

SQL> alter table WRI$_OPTSTAT_HISTGRM_HISTORY shrink space;

alter table WRI$_OPTSTAT_HISTGRM_HISTORY shrink space

*

ERROR at line 1:

ORA-10631: SHRINK clause should not be specified for this object

SQL> alter table WRI$_OPTSTAT_HISTGRM_HISTORY enable row movement;

Table altered.

SQL> alter table WRI$_OPTSTAT_HISTGRM_HISTORY shrink space;

alter table WRI$_OPTSTAT_HISTGRM_HISTORY shrink space

*

ERROR at line 1:

ORA-10631: SHRINK clause should not be specified for this object

报这个错误的原因是由于这张表有函数索引:

SQL> select owner,index_name from dba_indexes where table_name='WRI$_OPTSTAT_HISTGRM_HISTORY';

SYS I_WRI$_OPTSTAT_H_ST

SYS I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST

SQL> select index_owner,index_name from dba_ind_expressions where table_name='WRI$_OPTSTAT_HISTGRM_HISTORY';

SYS I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST

SYS I_WRI$_OPTSTAT_H_ST

只能用move来降HWM了，做完这个操作，要对相应的index重建.

SQL> alter table WRI$_OPTSTAT_HISTGRM_HISTORY move;

Table altered.

SQL> alter index I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild online;

Index altered.

SQL> alter index I_WRI$_OPTSTAT_H_ST rebuild online;

Index altered.

SQL> select * from v$sysaux_occupants where occupant_name='SM/OPTSTAT';

OCCUPANT_NAME OCCUPANT_DESC SCHEMA_NAM MOVE_PROCE MOVE_PROCEDURE_DESC SPACE_USAGE_KBYTES

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

SM/OPTSTAT Server Manageability - Optimizer Statistics History SYS *** MOVE PROCEDURE NOT APPLICABLE *** 12736

SQL> select sum(SPACE_USAGE_KBYTES) from v$sysaux_occupants;

SUM(SPACE_USAGE_KBYTES)

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

119488

SQL> select owner,segment_name,sum(bytes)/1024/1024 from dba_extents where segment_name in (

2 'WRI$_OPTSTAT_TAB_HISTORY','I_WRI$_OPTSTAT_TAB_OBJ#_ST','I_WRI$_OPTSTAT_TAB_ST','WRI$_OPTSTAT_IND_HISTORY','I_WRI$_OPTSTAT_IND_OBJ#_ST'

,'I_WRI$_OPTSTAT_IND_ST','WRI$_OPTSTAT_HISTHEAD_HISTORY','I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST','I_WRI$_OPTSTAT_HH_ST','WRI$_OPTSTAT_HISTGRM_HISTORY'

3 4 ,'I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST','I_WRI$_OPTSTAT_H_ST','WRI$_OPTSTAT_AUX_HISTORY','I_WRI$_OPTSTAT_AUX_ST','WRI$_OPTSTAT_OPR'

5 ,'I_WRI$_OPTSTAT_OPR_STIME','OPTSTAT_HIST_CONTROL$')

6 group by owner,segment_name

7 order by 3;

OWNER SEGMENT_NAME SUM(BYTES)/1024/1024

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

SYS OPTSTAT_HIST_CONTROL$ .015625

SYS WRI$_OPTSTAT_AUX_HISTORY .0625

SYS I_WRI$_OPTSTAT_OPR_STIME .0625

SYS I_WRI$_OPTSTAT_AUX_ST .0625

SYS WRI$_OPTSTAT_OPR .0625

SYS I_WRI$_OPTSTAT_IND_ST .1875

SYS I_WRI$_OPTSTAT_TAB_ST .1875

SYS I_WRI$_OPTSTAT_IND_OBJ#_ST .25

SYS I_WRI$_OPTSTAT_TAB_OBJ#_ST .3125

SYS WRI$_OPTSTAT_IND_HISTORY .3125

SYS WRI$_OPTSTAT_TAB_HISTORY .3125

SYS I_WRI$_OPTSTAT_H_ST .4375

SYS I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST .5625

SYS WRI$_OPTSTAT_HISTGRM_HISTORY .625

SYS I_WRI$_OPTSTAT_HH_ST 2

SYS I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST 3

SYS WRI$_OPTSTAT_HISTHEAD_HISTORY 4

17 rows selected.

从总体上看：sysaux释放了56M, 这正是SM/OPTSTAT释放出来的。测试时碰到一问题，当SGA是自动管理时，purge时会被MMON进程阻塞的。

部分转载自：https://blog.csdn.net/wu_wei_jie/article/details/6331911?locationNum=13&fps=1