ITPub博客

首页 > 数据库 > Oracle > sysaux 表空间不足问题处理

sysaux 表空间不足问题处理

Oracle 作者:听海★蓝心梦 时间:2013-12-25 12:21:26 0 删除 编辑

一现场,备份库alter 日志出现错误
ORA-1688: unable to extend table SYS.WRH$_SQLSTAT partition WRH$_SQLSTA_3344221469_3956 by 128 in                 tablespace SYSAUX
ORA-1688: unable to extend table SYS.WRH$_SQLSTAT partition WRH$_SQLSTA_3344221469_3956 by 128 in                 tablespace SYSAUX

根据错误提示是sysaux 表空间满了

从10G 开始引入的,以前一些使用独立表空间或系统表空间的数据库组件现在在SYSAUX表空间中创建。通过分离这些组件和功能,SYSTEM表空间的负荷得以减轻.反复创建一些相关对象及组件引起SYSTEM表空间的碎片问题得以避免。
如果SYSAUX表空间不可用,数据库核心功能将保持有效;使用SYSAUX表空间的特点将会失败或功能受限。可以从V$SYSAUX_OCCUPANTS view 查看情况

V$SYSAUX_OCCUPANTS

V$SYSAUX_OCCUPANTS displays SYSAUX tablespace occupant information.
Column                                  Datatype              Description
OCCUPANT_NAME                 VARCHAR2(64)      Occupant name
OCCUPANT_DESC                  VARCHAR2(64)      Occupant description
SCHEMA_NAME                     VARCHAR2(64)      Schema name for the occupant
MOVE_PROCEDURE               VARCHAR2(64)      Name of the move procedure; null if not applicable
MOVE_PROCEDURE_DESC      VARCHAR2(64)      Description of the move procedure
SPACE_USAGE_KBYTES         NUMBER              Current space usage of the occupant (in KB)

查看那些occupant空间占用情况:

select OCCUPANT_NAME,SPACE_USAGE_KBYTES/1024/1024 from V$SYSAUX_OCCUPANTS;
SELECT s.object_name from dba_objects s where s.object_name like '%OPTSTAT%' and s.object_type='TABLE';


在Oracle10中表空间SYSAUX引入,oracle把统计信息存储在这里,这也是为了更好的优化system表空间,
我们可以用视图V$SYSAUX_OCCUPANTS 查看,oracle有哪些数据存贮在SYSAUX中。


oracle的SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER的统计信息都存储在SYSAUX中
 
查询当前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
 


删除20天前的统计数据
SQL> exec dbms_stats.purge_stats(sysdate-20);
 
PL/SQL procedure successfully completed
 
查看当前有效的统计数据是到什么时间的

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
 

没有释放空间是因为“purge_stats”用delete的方式删除数据,虽然数据没了,但是HWM还没降下来,查看OPTSTAT使用哪些表,然后降低其高水位即可。
注意:占用空间的不一定都是object_name like '%OPTSTAT%'的对象,所以这个条件必要时可以去掉。

SQL> SELECT s.object_name from dba_objects s where s.object_name like '%OPTSTAT%' and s.object_type='TABLE';
 
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%';
 
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
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';
 
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
 
再次查看表空间的使用率,sysaux表空间确实减低不少。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/751371/viewspace-1064285/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-02-18

  • 博文量
    256
  • 访问量
    1197851