Oracle ORA-01555快照过旧的错误(转载)

第一篇文章：

 

首先了解Oracle在什么情况下会产生ORA-01555错误:

假设有一张6000万行数据的testdb表，预计testdb全表扫描1次需要2个小时，参考过程如下：

1、在1点钟，用户A发出了select * from testdb;此时不管将来testdb怎么变化，正确的结果应该是用户A会看到在1点钟这个时刻的内容。

2、在1点30分，用户B执行了update命令，更新了testdb表中的第4100万行的这条记录，这时，用户A的全表扫描还没有到达第4100万条。毫无疑问，这个时候，第4100万行的这条记录是被写入了回滚段，假设是回滚段UNDOTS1，如果用户A的全表扫描到达了第4100万行，是应该会正确的从回滚段UNDOTS1中读取出1点钟时刻的内容的。

3、这时，用户B将他刚才做的操作提交了，但是这时，系统仍然可以给用户A提供正确的数据，因为那第4100万行记录的内容仍然还在回滚段UNDOTS1里，系统可以根据SCN到回滚段里找到正确的数据，但要注意到，这时记录在UNDOTS1里的第4100万行记录已经发生了重大的改变：就是第4100万行在回滚段UNDOTS1里的数据有可能随时被覆盖掉，因为这条记录已经被提交了！

4、由于用户A的查询时间漫长，而业务在一直不断的进行，UNDOTS1回滚段在被多个不同的transaction使用着，这个回滚段里的extent循环到了第4100万行数据所在的extent，由于这条记录已经被标记提交了，所以这个extent是可以被其他transaction覆盖掉的！

5、到了1点45分，用户A的查询终于到了第4100万行，而这时已经出现了第4条说的情况，需要到回滚段UNDOTS1去找数据，但是已经被覆盖掉了，这时就出现了ORA-01555错误。

原因分析："报表"程序执行时间漫长，在程序查询的过程中其他用户对"报表"进行了更新，被更新的数据写入了回滚段，当程序到回滚段找数据时，发现数据已经被覆盖掉，于是就出现了ORA-01555错误。另外"报表"程序执行效率不高也会造成ORA-01555错误。

解决办法：

1、扩大回滚段，因为回滚段是循环使用的，如果回滚段足够大，那么那些被提交的数据就能保存足够长的时间，使那些大事务完成一致性读取。之前EBS系统UNDO表空间为9GB，目前为10GB。见下图:

2、增加undo_retention时间，因为UNDO回滚段是循环使用，里面的数据可能随时被循环覆盖掉，如果设置undo_retention时间更长，那么在retention规定的时间内，任何其他事务都不能覆盖这些数据。目前EBS系统undo_retention为10800秒(3个小时)。见下图：

3、最重要的一点就是优化程序相关查询语句，减少查询语句的一致性读，降低读取不到回滚段数据的风险。所有的出错信息都会纪录到数据库日志alert_PROD.log文件中，下图红线部分是一条SQL查询词句，ORA-01555很有可能是这条语句造成，把这条语句提供给开发人员来分析和优化程序代码。

ORA-01555 原因与解决：

前面提到了ORA-01555错误，那么现在来看一下ORA-01555错误是怎样产生的。由于回滚段是循环使用的，当事务提交以后，该事务占用的回滚段事务会被标记为非活动，回滚段空间可以被覆盖重用。那么一个问题就出现了，如果一个查询需要使用被覆盖的回滚段构造前镜像实现一致性读，那么此时就会出现Oracle著名的ORA-01555错误。

ORA-01555错误的另外一个原因是因为延迟块清除（Delayed Block Cleanout）。当一个查询触发延迟块清除时，Oracle需要去查询回滚段获得该事务的提交SCN，如果事务的前镜像信息已经被覆盖，并且查询SCN也小于回滚段中记录的最小提交SCN，那么Oracle将无从判断查询SCN和事务提交SCN的大小，此时出现延迟块清除导致的ORA-01555错误。

另外一种导致ORA-01555错误的情况出现在使用sqlldr直接方式加载（direct=true）数据时。当通过sqlldr direct=true 方式加载数据时，由于不产生重做和回滚信息，Oracle直接指定Cached Commit SCN 给加载数据，在访问这些数据时，有时会产生ORA-01555错误。

看下图的描述：假定在时间T用户A发出一条更新语句，更新SCOTT用户的SAL；用户B在Ty时间发出查询语句，查询SCOTT用户的SAL；用户A的更新在Tx时间提交，提交可能为快速提交块清除，也可能是延迟块清除；用户B的查询在Tz时间输出。

来看一下数据库在不同情况下的内部处理：

·如果 Ty < T < Tz < Tx ，那么查询需要构造一致性读，由于事务尚未提交，可以通过回滚段构造前镜像，完成一致性读取。

·如果 Ty < T < Tx < Tz ，由于Ty查询时间小于T事务更新时间，那么数据库需要构造一致性读取，而Tz查询完成时间大于Tx提交时间，那么前镜像就有可能被覆盖，不可获取。

如果Tx的提交方式为Fast Block Cleanout，那么回滚段信息不可用时就会出现一致性读ORA-01555错误。

如果Tx的提交方式为Delayed Block Cleanout，那么回滚段信息不可用时Oracle将无法判断Ty和Tx的时间先后关系。如果 Ty > Tx ，那么Oracle可以正常进行块清除，并将块清除后的数据返回给用户B；如果 Ty < T ，那么Oracle需要继续构造一致性读返回给用户B；Oracle无法判断这两种情况，就会出现延迟块清除ORA-01555错误。

ORA-01555的直观解释是“snapshot too old”，也就是快照太旧，其根本含义就是查询需要的前镜像过于“久远”，已经无法找到了。可以想象，如果一个历时数个小时或十几个小时的查询，如果最后遭遇ORA-01555错误而失败，会是多么令人沮丧的一件事。一直以来，ORA-01555都是ORACLE最为头痛的问题之一。

在Oracle 9i的文档中这样描述ORA-01555错误：

01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\" too small"

// *Cause: rollback records needed by a reader for consistent read are

//        overwritten by other writers

// *Action: If in Automatic Undo Management mode, increase undo_retention

//          setting. Otherwise, use larger rollback segments

可以看到，在Oracle 9i自动管理UNDO表空间模式下，UNDO_RETENTION参数的引入正是为了减少ORA-01555错误的出现。这个参数设置当事务提交之后（回滚段变得非活跃），回滚段中的前镜像数据在被覆盖前保留的时间，该参数以秒为单位，9iR1初始值为900秒，在Oracle 9iR2增加为10800秒。

显然该参数设置的越高就越能减少ORA-01555错误的出现，但是保留时间和存储空间是紧密相关的，如果UNDO表空间的存储空间有限，那么Oracle就会选择回收已提交事务占用的空间，置UNDO_RETENTION参数于不顾。

在Oracle 9i的AUM模式下，UNDO_RETENTION实际上是一个非担保（NO Guaranteed）限制。也就是说，如果有其他事务需要回滚空间，而空间出现不足时，这些信息仍然会被覆盖；从Oracle 10g开始，Oracle对于UNDO增加了Guarantee控制，也就是说，可以指定UNDO表空间必须满足UNDO_RETENTION的限制。当UNDO表空间设置为Guarantee，那么提交事务的回滚空间必须被保留足够的时间，如果UNDO表空间的空间不足，那么新的事务会因空间不足而失败，而不是选择之前的覆盖。

从各个不同版本回滚段的管理变迁，我们可以看出Oracle一直在进步。

Oracle提供了一个内部事件（10203事件）可以用来跟踪数据库的块清除操作，10203事件可以通过以下命令设置，设置后需要重新启动数据库该参数方能生效：

alter system set event="10203 trace name context forever" scope=spfile;

需要注意的是，可能存在另外一种情况，就是当执行延迟块清除时，回滚段或原回滚表空间已经被删除，此时Oracle仍然可以通过字典表UNDO$来获得SCN信息，执行块清除。

关于Oracle的提交处理及块清除机制是一个极其复杂的过程，本文对这部分内容进行了适当简化说明，旨在使大家能够对Oracle的回滚机制、块清除机制有所了解。

- The End -

 

第二篇文章：

写了段java操作数据库的代码

String getIPList="select t.dns_ip from t_dnscachetotal t where t.locid=0";
        String getLocid="select  t3.locid from (select max(t2.ipstart) ipstart,max(t2.ipend) ipend from t_GGMAP_IP t2 where t2.ipstart<=query_ip(?)) t1,t_GGMAP_IP t3 where t1.ipend>=query_ip(?) and t1.ipstart=t3.ipstart";
        String updateDnsCacheTotal="update t_dnscachetotal t set t.locid=? where t.dns_ip=?";

stmt=con.prepareStatement(getIPList);
        rs=pstmt.executeQuery();
        String ip;

        ResultSet rs2;
        int countupdate=0;
        while(rs.next()){
               ip=rs.getString(1);
               pstmt2 = con.prepareStatement(getLocid);
               pstmt2.setString(1, ip);
               pstmt2.setString(2, ip);
               rs2=pstmt2.executeQuery();
               int locid=-1;
               while(rs2.next()){
                   locid=rs2.getInt(1);
               }
               pstmt2.close();
               rs2.close();
               countupdate++;
               pstmt2=con.prepareStatement(updateDnsCacheTotal);
               pstmt2.setInt(1, locid);
               pstmt2.setString(2, ip);
               pstmt2.executeUpdate();
               pstmt2.close();

            }
        pstmt.close();
        rs.close();

 

大体就这样。。我删了一部分代码。

核心的问题就在于

while(rs.next()){
       
    }

因为rs.next实际上是对oracle某表持续的查询，而在循环中又在不断地update这个表，从而导致了这个1555错误，

ora 1555别人的例子 写道

首先了解Oracle在什么情况下会产生ORA-01555错误: 

假设有一张6000万行数据的testdb表，预计testdb全表扫描1次需要2个小时，参考过程如下： 
1、在1点钟，用户A发出了select * from testdb;此时不管将来testdb怎么变化，正确的结果应该是用户A会看到在1点钟这个时刻的内容。 
2、在1点30分，用户B执行了update命令，更新了testdb表中的第4100万行的这条记录，这时，用户A的全表扫描还没有到达第4100万条。毫无疑问，这个时候，第4100万行的这条记录是被写入了回滚段，假设是回滚段UNDOTS1，如果用户A的全表扫描到达了第4100万行，是应该会正确的从回滚段UNDOTS1中读取出1点钟时刻的内容的。 
3、这时，用户B将他刚才做的操作提交了，但是这时，系统仍然可以给用户A提供正确的数据，因为那第4100万行记录的内容仍然还在回滚段UNDOTS1里，系统可以根据SCN到回滚段里找到正确的数据，但要注意到，这时记录在UNDOTS1里的第4100万行记录已经发生了重大的改变：就是第4100万行在回滚段UNDOTS1里的数据有可能随时被覆盖掉，因为这条记录已经被提交了！ 
4、由于用户A的查询时间漫长，而业务在一直不断的进行，UNDOTS1回滚段在被多个不同的transaction使用着，这个回滚段里的extent循环到了第4100万行数据所在的extent，由于这条记录已经被标记提交了，所以这个extent是可以被其他transaction覆盖掉的！ 
5、到了1点45分，用户A的查询终于到了第4100万行，而这时已经出现了第4条说的情况，需要到回滚段UNDOTS1去找数据，但是已经被覆盖掉了，这时就出现了ORA-01555错误。

 

明显我是犯了同样的错误。而解决办法大致有三个

首先：

SQL> show parameter undo
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

 

可以看到我们是自动管理undo的，(11g)，另外undo_retention时间是900s,这个是可以考虑放大的，

写道

关于初始化参数UNDO_RETENTION的设置，严格说起来也是与UNDO表空间有关系，但是思量再三，我觉着还是有必要单拎出来详细介绍。 

该参数用来指定UNDO段中数据保存的最短时间，以秒为单位，是一个动态参数，完全可以在实例运行时随时修改，通常默认是900秒，也就是15分钟。 

首先要注意，UNDO_RETENTION只是指定UNDO段中数据的过期时间，并不是说，UNDO段中的数据一定会在UNDO表空间中保存15分钟。如一个新事务开始的时候，如果此时UNDO表空间已经被写满，则新事务的数据会自动覆盖已提交事务的数据，而不管这些数据是否已过期，因此呢，这就又关联回了第一点，当你创建一个自动管理的UNDO表空间时，还要注意其空间大小，要尽可能保证UNDO表空间有足够的存储空间。 

同时还要注意，也并不是说，UNDO_RETENTION中指定的时间一过，已经提交事务中的数据就立刻无法访问，当超出UNDO_RETENTION参数指定的时间后，这部分数据占用的空间将会被标识为可重用，不过只要不被别的事务触发的数据覆盖，它会仍然存在，并可以随时被Flashback特性引用。如果你的UNDO表空间足够大，而数据库又不是那么繁忙，那么其实UNDO_RETENTION参数的值并不会影响到你，哪怕你设置成1（这么说好像绝对了点，大家一定要注意理解，别钻牛角尖），只要没有事务去覆盖UNDO数据，这部分数据就会持续有效。因此呢，再次重复那句话，要注意UNDO表空间的大小，保证其有足够的存储空间。 

最后，只有在一种情况下，UNDO表空间能够确保UNDO中的数据在UNDO_RETENTION指定时间过期前一定有效，就是为UNDO表空间指定RETENTION GUARANTEE，指定之后，不会覆盖UNDO表空间中未过期的UNDO数据，例如： 

SQL> ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE; 如果想禁止UNDO表空间RETENTION GUARANTEE，例如： 

SQL> ALTER TABLESPACE UNDOTBS1 RETENTION NOGUARANTEE; 转了一圈，问题又回来了，既然它看起来有用又像没有用，为什么还要设置它呢？嘿嘿，就我理解，其存在的真实用途，就是提醒你UNDO表空间很重要，给它指定分配一个合适的大小更重要哟。 

 但是从这篇文章中明显发现，undo_retention参数意义很小，而且默认的TABLESPACE UNDOTBS1 一般都是NOGUARANTEE，这个是一个考虑解决1555错误的方法，但是我个人觉得大部分情况绝不是因为undo_retention过小引起的，应该都是因为UNDOTBS1 占满了的缘故，所以

1、扩大回滚段

因为回滚段是循环使用的，如果回滚段足够大，那么那些被提交的数据信息就能保存足够长的时间是那些大事务完成一致性读取。

2、增加undo_retention时间

在undo_retention规定的时间内，任何其他事务都不能覆盖这些数据。

3、优化相关查询语句，减少一致性读。

减少查询语句的一致性读，就降低读取不到回滚段数据的风险。这一点非常重要！

4、减少不必要的事务提交

提交的事务越少，产生的回滚段信息就越少。

5、对大事务指定回滚段

通过以下语句可以指定事务的回滚段：

SET TRANSACTION USE ROLLBACK SEGMENTrollback_segment；

我觉得标红的都是可以考虑的方案，

我的解决方案包括了

1.while(rs.next()){
       
    }去掉，不在rs循环内执行update，而是把rs数据全部读出，然后在进行逐条update,

2.无非就是标红的那些了

 

第三篇文章：

ORA-01555 快照过旧

快照过旧这个问题，自从接触 oracle 开始就频繁遇到，到目前为止可能已接触过不止 几十次。 要写 case 的话， 也许不止一二百页。 这个错误产生在多个方面， select 时， 如 update 时，delete 时，exp/expdp 时，好像产生的原因有很多。

产生原因

先看一下 oracle 是如何定义这个 ora 错误的。 01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\" too small" // *Cause: rollback records needed by a reader for consistent read are // overwritten by other writers

原因一：SQL 语句执行时间太长，或者 UNDO 表空间过小，或者事务量过大，或者过 于频繁的提交，导致执行 SQL 过程中进行一致性读时，SQL 执行后修改的前镜像（即 UNDO 数据）在 UNDO 表空间中已经被覆盖，不能构造一致性读块（CR blocks） 。

那有人会问为什么 oracle 要覆盖这条 undo 记录， 不设置永久保留或保留时间。 那我们 需要从 undo 表空间说起。 我们都知道 undo 表空间中保留的对象是事务的前镜像， 也就是对 修改记录的备份。那么说要永久保留的这些记录的话是不现实的，需要 N 多 undo 表空间, 这 显 然 不 合 适 。 但 可 对 undo 记 录 设 置 一 个 保 留 时 间 。 Oracle 使 用 这 个 参 数 控 制 undo_retention,10g 中这个参数的默认设置是 900s。这个参数让我们进入了一个误区。很 多时候，我们因为 ora-01555 错误去修改这个 undo_retention，让这个参数更大，但是很 难收到良好的效果，可能依然会报快照过旧。下面我们来分析一下 首先我们要了解 undo 表空间的 extent 包括哪几部分 ACTIVE 活动状态。说明当前这个数据区正在为某个正在进行的事务所使用着。 EXPIRED 已过期。 这个已分配的数据区已经完成了它的使命， 随时可能被分配给其它新的事务使 用。 UNEXPIRED 未过期。这个分配的数据区已经不属于任何的活动事务，但是由于 UNDO RETENTION 设 置的需要，一般情况下不会被回收重用 我们还需要明确以下几点 1、首先undo_retention在10G不需要人为的去设置它,就算你设置了,oracle还是会根 据数据库的使用自动调整它;

2、如果你的undo_tablespace是自动扩展的,oracle至少会保留undo_retention设置时间之内的extent不会被expired,如果事务不是很多的时候，有些unexpired的undo extent 不会别立即释放,就算你手动修改了undo_retention也没用,直到下次有事务需要undo的时候,在expired;

3、如果你的undo_tablespace不是自动扩展的,也就是说大小是固定的,那么undo_retention这个参数会别忽略,会根据事务的大小及时expired不需要的undo extent， 如果事务非常大，有可能会提前 覆盖unexpired extent,循环使用,这样会造成undo非常紧 张,可能会出更严重的问题

4、如果

你的 undo tablespace 启用了 guarantee,那么不管你 undo tablespace 是自动 扩 展 还 是 固 定 大小 , 那 么都 会 保 证 undo_retention 之 内 的 unexpired exten 不 会 被 expired.

有以上信息我们可得出以下结论 undo_retention 这个参数只有在 undo 表空间启动了自动表空间扩展或 guarantee 参数 启用时才生效。其他情况下设置这个参数基本无用，快照过旧依然会报。

另外通过以上信息我们还可以了解到监控 undo 表空间利用率在默认情况下（也就是在 undo 表空间非自动扩展或 noguarantee） 我们可以通过一下语句监控到 undo 表空间的利用情况（这是真实的利用情况） select tablespace_name, status, round(sum(bytes) / 1048576, 2) size_mb, count(*) extent_count from dba_undo_extents group by tablespace_name, status order by tablespace_name, status;

拿一个生产库为例。我们通过以上语句，观察到 undo 表空间的使用情况，undotbs2 暂无 active 的事 务，而 undotbs1 active 的为 64M

但我们以平时监控数据表空间的语句来监控 undo 的话

select t.* from (SELECT D.TABLESPACE_NAME, SPACE "SUM_SPACE(M)" BLOCKS SUM_BLOCKS, SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)" ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)" FREE_SPACE "FREE_SPACE(M)" FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE, SUM(BLOCKS) BLOCKS

FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D, (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) UNION ALL --if have tempfile SELECT D.TABLESPACE_NAME, SPACE "SUM_SPACE(M)" BLOCKS SUM_BLOCKS, USED_SPACE "USED_SPACE(M)" ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)"SPACE - USED_SPACE "FREE_SPACE(M)" FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE, SUM(BLOCKS) BLOCKS FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D, (SELECT TABLESPACE, ROUND(SUM(BLOCKS * 8192) / (1024 * 1024), 2) USED_SPACE FROM V$SORT_USAGE GROUP BY TABLESPACE) F WHERE D.TABLESPACE_NAME = F.TABLESPACE(+)) t WHERE T.TABLESPACE_NAME like 'UNDO%' order by "USED_RATE(%)" desc;

显然第二个监控语句误导了我们，undotbs1 并未 use 91.34%，只 use 64M

原因二：SQL 语句执行过程中，访问到的块，在进行延迟块清除时，不能确定该块的事 务提交时间与 SQL 执行开始时间的先后次序。 这种情况很少。 关于这个原因，Thomas kyte 在《9i/10g/11g 编程艺术》一书中的 P340-344 中有详细 解。 简单的解释： 当一个查询触发延迟块清除时， Oracle 需要去查询回滚段获得该事务的提交 SCN， 如果 事务的前镜像信息已经被覆盖，并且查询 SCN 也小于回滚段中记录的最小提交 SCN，那么 Oracle 将无从判断查询 SCN 和事务提交 SCN 的大小， 此时出现延迟块清除导致的 ORA-01555 错误

解决办法

ORA-01555 错误在 Oracle 8i 及之前的版本最多。从 9i 开始的 undo 自动管理，至现在 的 10g、11g 中的 undo auto tuning，使得 ORA-01555 的错误越来越少。但是这个错误，仍 然不可避免，但我们可以做到尽量的减少这个错误发生的概率。

我们看下 oracle 给出的解决办法 // *Action: If in Automatic Undo Management mode, increase undo_retention // setting. Otherwise, use larger rollback segments 具体办法如下 针对第一种原因，我们可以用以下方法处理

（1）优化出错的 SQL，减少查询的时间，首选方法

（2）避免频繁的提交

（3）增加 undo_retention 时间，默认只有 15 分钟

（4）增加 UNDO 表空间大小

第一种方法最可取，其次是第二种方法。第三种方法不在万不得已时不要使用。笔者曾 在这种方法下有过惨痛教训。

有个项目上线割接当晚，爆出了大量的 ORA-01555 错误，由于时间短，工作量大，当时应用侧无法修改脚本，我们只能使用了下下策 SQL> alter tablespace undotbs1 retention guarantee; Tablespace altered. SQL> alter system set undo_retention=14400; System altered.

充分考虑了查询时间后我们把保留时间定为 4 个小时。由于当晚事务量大而多，undo 表空间涨的很快，为了不使查询报错，我们依然决定扩 undo 表空间。以空间来换取时间。 对于第二种原因的解决办法 大批量的 UPDATE 或 INSERT 会导致块清除（block cleanout） ，所以在大批量 UPDATE 或 大量加载之后使用 DBMS_STATS 收集相关对象的统计信息，加载之后完成这些对象的清理。