oracle1555错误,ORA-1555经典的错误

现象：

应用的夜维从夜里00:00开始执行，但因为hang的原因(暂时猜测为夜维处理的某条数据和当前应用正常处理的某条数据相同，出现前后等待同一资源锁的现象)，直到第二天白天09:25左右才继续执行，但此时应用日志记录：

snapshot too old: rollback segment number 29 with name "_SYSSMU29$" too small

原因分析：

因hang导致夜维的DELETE语句一直处于等待状态(超过一天)，直到资源锁释放，但此时由于开始存放于UNDO中的前镜像超过UNDO_RETENTION参数设置的时间，且这是高并发的一个系统，很快可能就会被应用session覆盖UNDO中的记录，导致无法找到UNDO中的记录产生一致性读，因此报错ORA-1555，此次执行失败。

虽然分析可能是这个原因，但是是什么语句让这个系统的夜维语句hang，还没有找到，有待进一步分析。。。

引申：

不过从这个报错现象可以接触到ORA-1555这个经典的错误号，尤其是在生产中，也是一种不多见的情况，尤其在现在UNDO基本都是用Oracle自动管理方式，且磁盘空间分配都比较大的情况下。

这个ORA-1555的错误是Oracle回滚段错误中的一种经典。UNDO用于记录DML操作数据的前镜像，ORA-1555的错误简单用一句话总结，我觉得就是当DML语句需要用UNDO记录的数据找到前镜像时，该记录已经被覆盖，导致无法利用UNDO中的记录完成一致性读。当然Oracle也有UNDO_RETENTION等参数避免这种情况的产生，但仍旧可能发生，原因有多种，解决方法也有多种，下面就简单说明介绍下。

首先，Tom、hellodba等高人也对ORA-1555有过经典的介绍。从原因来讲，ORA-1555的错误原因归为两种，一是一致性读，一个是延迟块(锁)清除。

和ORA-1555相关的参数：

1、UNDO_RETENTION。

UNDO_RETENTION参数设置了回滚段中被提交或回滚的数据强制保留时间，但并不是说超过这个时间，回滚段中的数据就会被清除，而是等待后面的事务产生的回滚数据覆盖之前的。

2、对于Oracle 9i及以上版本，有两种管理UNDO的方法，由UNDO_MANAGEMENT参数指定，手动管理UNDO和自动管理UNDO的区别：手动管理是会回绕的，会尽可能地重用UNDO空间，Oracle会扩展UNDO段，而不是回绕，从而保证UNDO_RETENTION的时间要求。因此从这可以看出，为了避免ORA-1555，使用自动管理UNDO的方法也许可以一定程度上降低概率。

概念介绍：

Oracle通过回滚段进行一致性读，即避免了脏读，又大大减少了系统的阻塞、死锁问题。Oracle更新数据块(Data Block Oracle中最小的存储单位)时，会在两个地方记录下这一更新动作。一个是重做段(Redo Segment)，是用于数据库恢复(Recover)用的。一个是回滚段(UNDO Segment)，而回滚段是用于事务回滚(Rollback)的(我们只关心回滚段了)。并在数据块头部标示出来是否有修改数据。一个语句在读取数据快时，如果发现这个数据块是在它读取的过程中被修改的(即开始执行读操作时并没有被修改)，就不直接从数据块上读取数据，而是从相应的回滚段条目中读取数据。这就保证了最终结果应该是读操作开始时的那一时刻的快照(snapshot)，而不会受到读期间其他事务的影响。这就是Oracle的一致性读，也可以叫做多版本(Multi-Versioning)。

当Oracle更新数据块时，会在回滚段(UNDO Segment)记录下这一更新动作。并且产生一个Cleanout SCN，在回滚段中，会产生对应的Transaction ID以及相应的数据记录镜像。并在对应的数据记录上，产生锁标志。在事务提交(commit)前，会在数据块的头部记录下这个Cleanout SCN(Csc)号、Undo Block Address(Uba)和Transaction ID(Xid)；并且在在对应Interested

Transaction List(Itl)中设置锁标志，记录这个事务在这数据块中产生的锁的数目；同时在对应修改的数据记录上打上行级锁标志，并映射到对应的Itl去。当提交时，并不会一一清除掉所有锁标志，而是给对应的Itl打上相应标志，告诉后面访问该数据块的事务，相应的事务已经提交。这就叫做快速提交(Fast Commit)。而后面访问该数据块的的事务就先检查锁标志和对应的事务状态，如果发现前面的事务没有提交，并且要访问的数据记录被锁住了，就被阻塞；否则就清除相应的锁标志，并提交自己的锁标志，再重复以上动作。这就事延迟块清除。

如果大事务接触到了非常多的块，并且到了缓冲区缓存的10%以上，此时就会出现待清理的块，并未由COMMIT操作清理，即不是FAST COMMIT，没有其它事务DML接触这些块，而是SELECT一个表时就有可能出现ORA-1555的错误。

另外，看到网上很多验证ORA-1555的错误实验，都是用SELECT语句测试的，但其实应该是“事务”或“查询”语句需要UNDO中数据时，出现记录被覆盖的情况下都有可能报这个错，因此开始介绍的夜维报错就是DELETE语句报的ORA-1555，且这个夜维比较特殊的地方就是他是若干条(22条)DELETE语句在一个事务中，即都执行完成后才一次COMMIT，这样无形当中增加了事务的复杂度，但凡其中一条语句等待，则其他语句就无法提交，也只能等待，虽然这是由业务决定的，但这种情况还是应该避免，当然这是另一个话题了。

实例：UNDO空间太小导致的ORA-1555。

创建一个2M大小，不能自动扩展的UNDO空间。

SQL> create undo tablespace undo_small datafile ‘/opt/oracle/oradata/bisal/undo_small.dbf‘ size 2m autoextend off;

将其设置为系统UNDO空间。

SQL> alter system set undo_tablespace = undo_small;

System altered.

创建一张测试表。(注意：这里使用dbms_random.random是为了将行弄乱，使他们不至于认为有某种顺序，从而得到随机的分布，因为CTAS方式建表是力图按照查询获取的顺序将行放在块中。)

SQL> create table t as select * from all_objects order by dbms_random.random;

Table created.

创建主键。

SQL> alter table t add constraint t_undo_pk primary key(object_id);

Table altered.

收集表的统计信息。

SQL> exec dbms_stats.gather_table_stats(user, ‘T‘, cascade=>true);

PL/SQL procedure successfully completed.

重复更新表中所有数据。

SQL> begin

for x in (select rowid rid from t)

loop

update t set object_name = lower(object_name) where rowid = x.rid;

commit;

end loop;

end;

/

PL/SQL procedure successfully completed.

上述语句执行过程中，创建查询语句，这里使用DBMS_LOCK.SLEEP(0.01)来模拟查询单次时间是0.01秒，由于是随机插入到表中的，因此此处相当于随机地查询表中的块。这个查询语句执行几秒就可能失败。

declare

cursor c is

select /*+first_rows*/ object_name

from t

order by object_id;

l_object_name t.object_name%type;

l_rowcnt number:=0;

begin

open c;

loop

fetch c into l_object_name;

exit when c%notfound;

dbms_lock.sleep(0.01);

l_rowcnt := l_rowcnt + 1;

end loop;

close c;

exception

when others then

dbms_output.put_line(‘rows fetched = ‘|| l_rowcnt);

raise;

end;

/

(注：报错

l_rowcnt number:=0;

*

ERROR at line 8:

ORA-06550: line 8, column 4:

PLS-00103: Encountered the symbol "L_ROWCNT" when expecting one of the

following:

:= ( ; not null range default character

The symbol ";" was substituted for "L_ROWCNT" to continue.

可能是l_object_name t.object_name%type少分号)

报错是：

declare

*

ERROR at line 1:

ORA-01555: snapshot too old: rollback segment number 11 with name "_SYSSMU11$" too small

ORA-06512: at line 21

总结：

对于报错，原因在于SELECT语句是按照object_id的索引进行读取，(INDEX FULL SCAN)，由于之前数据是按照随机顺序插入得到的，因此此处是在全表上执行随机读，这样就可能出现：SELECT读到的数据可能是不同块中的，此时UPDATE更新数据并提交，标识UNDO中该记录可被覆盖，由于UNDO空间较小，因此出现SELECT读取的UNDO块被UPDATE更新、提交而被覆盖的可能性就会变大，于是出现了ORA-1555的错误。

为了解决这种问题，将UNDO设置为可扩展，让Oracle自动管理UNDO，可以最大幅度地扩展UNDO容量，满足UNDO_RETENTION时间要求的同时，也保证了SELECT可以读到的块不会被UPDATE提交所覆盖。

SQL> column file_name new_val F

SQL> select file_name from dba_data_files where tablespace_name=‘UNDO_SMALL‘;

FILE_NAME

--------------------------------------------------------------------------------

/opt/oracle/oradata/bisal/undo_small.dbf

SQL> Alter database datafile ‘&F‘ autoextend on

2  next 1m

3  maxsize 2048m;

old   1: Alter database datafile ‘&F‘ autoextend on

new   1: Alter database datafile ‘/opt/oracle/oradata/bisal/undo_small.dbf‘ autoextend on

Database altered.

SQL> select bytes/1024/1024 from dba_data_files where tablespace_name=‘UNDO_SMALL‘;

BYTES/1024/1024

---------------

4

此时再执行上述UPDATE和SELECT语句，即可执行完成。

原文：http://blog.csdn.net/bisal/article/details/18187635