由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

看看数据文件有多大！天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以下，只不过手动麻烦些，希望大家有更好的办法。