(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_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
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
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.
################################################
[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';
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;
2972 rows selected.
回收方式:参考:
##############################################################
自己尝试回收32G的sysaux表空间:
自己尝试回收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
#######
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$_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;
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
Database altered
大家可以使用同样的办法使得SYSAUX缩小到500M以下,只不过手动麻烦些,希望大家有更好的办法。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26777968/viewspace-741983/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26777968/viewspace-741983/