ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ora-1555小结

ora-1555小结

原创 Linux操作系统 作者:myownstars 时间:2012-06-18 16:31:16 0 删除 编辑

Ora-01555错误会伴随如下一条信息

rollback segment number string with name "string" too small

string一般有三种类型

 1    _SYSSMU1$

 2    SYSTEM

3    “”,没有具体的信息,一般是由lob undo不足引起的

接下来一一简略介绍

首先说一下第一种情形

10g引入自动回滚表空间管理的同时还引入了automatic undo retention,由隐含参数_undo_autotune控制,默认为true

MMON30秒计算maxquerylen,进而计算出tuned_undorention,若tuned_undoretention < MAXQUERYLEN,则可能出现ora-1555

可通过v$undostat查询这两个字段,该视图每10分钟一个周期保存系统中的undo信息

query不可优化且该错误频繁出现,则需考虑增加undo表空间大小,计算公式如下

Undospace = undo_retention * undo_blocks * db_block_sizeundo_blocks即每秒钟生成的最大undo block数目,可由以下sql获取

undoblks/((end_time-begin_time)*86400) "Peak Undo Block Generation" FROM v$undostat WHERE undoblks=(SELECT MAX(undoblks) FROM v$undostat);

某些情况下automatic undo retention可能引发enq: US – contention http://space.itpub.net/15480802/viewspace-696089

 

而第二种情况,则是由system undo segment不足引发的,针对数据字典的操作(譬如drop tablespace)sys用户的DML会消耗此undo segment,通常应对的方法是手工将其扩容

system表空间采用手工管理,则运行如下script

--先将undo设置为手动管理,然后以sys用户执行DML生成大量undo直至system undo segment扩容到指定值,而后将undo设置自动管理并重启

connect / as sysdba
alter system set undo_management = MANUAL scope=spfile
shutdown immediate
startup
create table test (t1 number, t2 varchar2(10));
begin
for i in 1.. 300000 loop
insert into test values (i,'AAAAAAAAAA');
end loop;
end;
/
delete test;
commit;
alter system set undo_management = AUTO scope=spfile;
shutdown immediate
startup

system 表空间为字典管理,运行如下

set echo on
set feedback on

alter system set "_smu_debug_mode"=4;
drop table helper_for_1555;
create table helper_for_1555 (col1 varchar2(4000));

insert into helper_for_1555 values (rpad('a', 3999));
declare
a number;
begin
for a in 1..18 loop
insert /*+ APPEND +*/ into helper_for_1555 select * from helper_for_1555;
commit;
end loop;
end;
/

rem alter SYSTEM rbseg's storage params
alter rollback segment system storage (next 256M);
alter rollback segment system storage (optimal 1024M);
select segment_name, blocks, bytes, extents from  dba_segments
   where segment_type='ROLLBACK';

rem bloat the segment
alter system set "_in_memory_undo"=false;
set transaction use rollback segment SYSTEM;
delete from helper_for_1555;
rollback;

select segment_name, blocks, bytes, extents from dba_segments
   where segment_type='ROLLBACK';

alter system set "_smu_debug_mode"=0;
alter system set "_in_memory_undo"=true;

 

对于最后一种由lob引起的ora-1555

具体可参照

http://space.itpub.net/15480802/viewspace-708843

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

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

注册时间:2010-03-18

  • 博文量
    375
  • 访问量
    3040645