由ORA-1691引发的超大SYSAUX表空间整理

(1) 在大数据量插入的时候的日志报错：

Mon Jul 09 04:01:02 CST 2012
ORA-1691: unable to extend lobsegment SYS.SYS_LOB0000008983C00004$$ by 128 in tablespace              SYSAUX
ORA-1691: unable to extend lobsegment SYS.SYS_LOB0000008983C00004$$ by 8192 in tablespace              SYSAUX
ORA-1691: unable to extend lobsegment SYS.SYS_LOB0000008983C00004$$ by 128 in tablespace              SYSAUX
ORA-1691: unable to extend lobsegment SYS.SYS_LOB0000008983C00004$$ by 8192 in tablespace              SYSAUX

ORA-1691: unable to extend lobsegment SYS.SYS_LOB0000003717C00007$$ by 128 in tablespace              SYSAUX
ORA-1691: unable to extend lobsegment SYS.SYS_LOB0000003717C00007$$ by 8192 in tablespace              SYSAUX
ORA-1691: unable to extend lobsegment SYS.SYS_LOB0000003717C00007$$ by 128 in tablespace              SYSAUX

 

看看数据文件有多大！天32G的SYSAUX!

-rw-r----- 1 oracle oinstall  32G 08-24 13:24 sysaux01.dbf

ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_358854564_1851 by 128 in    

            tablespace SYSAUX
ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_358854564_1851 by 128 in    

            tablespace SYSAUX

 

(2)  初步查看错误信息
[oracle@dmdb ~]$ oerr ora 1688
01688, 00000, "unable to extend table %s.%s partition %s by %s in tablespace %s"
// *Cause:  Failed to allocate an extent of the required number of blocks for
//          table segment in the tablespace indicated.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
//          files to the tablespace indicated.
[oracle@dmdb ~]$ oerr ora 1691
01691, 00000, "unable to extend lob segment %s.%s by %s in tablespace %s"
// *Cause:  Failed to allocate an extent of the required number of blocks for
//          LOB segment in the tablespace indicated.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
//          files to the tablespace indicated.
################################################

（3）可以看到下面两个表是把SYSAUX撑大的源头

 SELECT us.SEGMENT_NAME,us.SEGMENT_TYPE,bytes/1024/1024/1024 GB,us.TABLESPACE_NAME,ul.table_name
FROM user_segments us,user_lobs ul
WHERE us.SEGMENT_NAME = ul.SEGMENT_NAME   AND ul.table_name = 'MGMT_BPEL_CONFIGFILES_UPLOAD';

OWNER      SEGMENT_NAME                   SEGMENT_TYPE                 MB
---------- ------------------------------ -------------------- ----------
SYS        SYS_LOB0000008983C00004$$      LOBSEGMENT                29212
SYS        SYS_LOB0000003717C00007$$      LOBSEGMENT                 3062

 

（4）查看dba_LOBS 看到是如下两个表

TABLE_NAME                              SEGMENT_NAME
------------------------------ ------------------------------

WRH$_SQLTEXT                          SYS_LOB0000008983C00004$$

WRI$_ADV_OBJECTS                   SYS_LOB0000003717C00007$$

##############################################################参考文章 是因为BUG导致WRH$_SQLTEXT表数据没用自动删除。
http://blog.csdn.net/lwei_998/article/details/6532443

这样以后解决：
SQL> truncate table WRH$_SQLTEXT;

####
这篇文章页很好
http://wenku.baidu.com/view/daf642728e9951e79b89278c.html

2972 rows selected.

回收方式：参考：

http://space.itpub.net/7199859/viewspace-193116

 

##############################################################
自己尝试回收32G的sysaux表空间：

 

根据BLOCK_ID 定位对象，删除然后根据元数据DDL重建

select max(BLOCK_ID) from dba_extents where tablespace_name='SYSAUX';

select segment_name,segment_type,owner from dba_extents where tablespace_name='SYSAUX' and BLOCK_ID=4194177;

SQL> drop table wrh$_latch purge;

然后根据元数据DDL重建

 

 

（5）批量删除：尝试缩小至5G   经过计算  5G/8k=655360 所以要重新清理大于BLOCK_ID=655360的对象：

select segment_name,segment_type,owner from dba_extents where tablespace_name='SYSAUX' and BLOCK_ID>655360;
 
SEGMENT_NAME                                                                     SEGMENT_TYPE       OWNER
-------------------------------------------------------------------------------- ------------------

------------------------------
WRI$_ADV_RECOMMENDATIONS                                                         TABLE              SYS
WRI$_ADV_ACTIONS                                                                 TABLE              SYS
WRI$_ADV_RATIONALE                                                               TABLE              SYS
WRI$_ADV_RATIONALE                                                               TABLE              SYS
WRI$_OPTSTAT_TAB_HISTORY                                                         TABLE              SYS
WRI$_OPTSTAT_IND_HISTORY                                                         TABLE              SYS
WRI$_OPTSTAT_HISTHEAD_HISTORY                                                    TABLE              SYS
WRI$_OPTSTAT_HISTHEAD_HISTORY                                                    TABLE              SYS
SCHEDULER$_EVENT_LOG                                                             TABLE              SYS
WRH$_BG_EVENT_SUMMARY                                                            TABLE              SYS
WRH$_ENQUEUE_STAT                                                                TABLE              SYS
WRH$_SHARED_POOL_ADVICE                                                          TABLE              SYS
WRI$_ADV_PARAMETERS_PK                                                           INDEX              SYS
I_WRI$_OPTSTAT_IND_OBJ#_ST                                                       INDEX              SYS
I_WRI$_OPTSTAT_IND_ST                                                            INDEX              SYS
I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST                                                    INDEX              SYS
#######

SQL> select 'drop table'||' '||segment_name||' purge;' from dba_extents where tablespace_name='SYSAUX' and 

SEGMENT_TYPE='TABLE'  and BLOCK_ID>655360 ;

 

drop table WRI$_ADV_RECOMMENDATIONS purge;
drop table WRI$_ADV_ACTIONS purge;
drop table WRI$_ADV_RATIONALE purge;

drop table WRI$_OPTSTAT_TAB_HISTORY purge;
drop table WRI$_OPTSTAT_IND_HISTORY purge;
drop table WRI$_OPTSTAT_HISTHEAD_HISTORY purge;
drop table WRI$_OPTSTAT_HISTHEAD_HISTORY purge;
drop table SCHEDULER$_EVENT_LOG purge;
drop table WRH$_BG_EVENT_SUMMARY purge;
drop table WRH$_ENQUEUE_STAT purge;
drop table WRH$_SHARED_POOL_ADVICE purge;

 alter index WRI$_ADV_PARAMETERS_PK rebuild;

 

（6）之后使用PLSQL查看表的元数据，然后执行重新创建的脚本即可。

这样就可以把SYSAUX表空间缩小到5G大小对应655360个8K块了。

SQL> alter database datafile '/home/oracle/product/oradata/dmdb1/sysaux01.dbf' resize 5g;
 
Database altered

 

大家可以使用同样的办法使得SYSAUX缩小到500M以下，只不过手动麻烦些，希望大家有更好的办法。

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

转载于:http://blog.itpub.net/26777968/viewspace-741983/