【Oracle 11g】ORA-01555: snapshot too old(快照过旧)错误

这个错误会导致：接收到这个错误的查询无法继续处理。

引起这个错误的原因有3个：

• undo段太小，不足以在系统上执行工作。
• 你的程序跨COMMIT获取（实际上这是前一点的一个变体）。
• 块清除。

解决方案

• 适当地设置参数UNDO_RETENTION(要大于执行运行时间最长的事务所需的时间）。可以用V$UNDOSTAT 来去顶长时间运行的查询的持续时间。另外，要确保磁盘上已经预留了的空间，使undo段能根据请求的UNDO_RETENTION增大。
• 使用手动undo管理时加大或增加更多的回滚段。这样在长时间的运行的查询执行期间，覆盖undo数据的可能性就能降低。这种方法可以解决上述的所有3个问题。请注意，不要将此方法作为首选方法。强烈建议采用自动undo管理。
• 减少查询的运行时间（调优）。如果可能的话，这绝对是一个好办法，所以应该首先尝试这种方法。这样就能降低对undo段的需求，不需要太大的undo段。这种方法可以解决上述的所有3个问题。
• 收集相关对象的统计信息。这样有助于避免前面所列的第三点。由于大批量的UPDATE或者INSERT会导致块清楚（block cleanout），所以需要在大批量UPDATE或者大量加载之后以某种方式收集统计信息。

实验一、创造一个snapshot too old并解决它

制造错误

连接数据库，并建立undo表空间

C:UsersAdministrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期一 9月 5 22:36:07 2016

Copyright (c) 1982,2010,Oracle.  All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning,OLAP,Data Mining and Real Application Testing options

SQL> create undo tablespace undo_small
  2  datafile '/tmp/undo.dbf' size 2m
  3  autoextend off
  4  /

表空间已创建。

我的oracle是安装在E盘的。如下是我的oracle_home（这个是我自己配置的，oracle安装的那个目录）。所以如上的行为，会在e:tmp目录中创建一个名为“undo.dbf”并占用磁盘空间2M的文件。

C:UsersAdministrator>set oracle_home
ORACLE_HOME=E:appAdministratorproduct11.2.0dbhome_1

设置undo表空间

SQL> alter system set undo_tablespace = undo_small;

创建一个把行打乱（order by dbms_random.random）的表t

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

看看数据量

SQL> select count(*) from t; COUNT(*)
----------
     71817

增加主键并执行存储过程（dbms_stats.gather_table_stats）

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

表已更改。

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

PL/SQL 过程已成功完成。

如下两个存储过程需要同时执行。

SQL> begin
  2     for x in (select rowid rid from t)
  3     loop
  4        update t set object_name = upper(object_name) where rowid = x.rid;
  5        commit;
  6     end loop;
  7     end;
  8     /

PL/SQL 过程已成功完成。

我是另外打开一个sqlplus,连接数据库，运行如下sql。然后立马运行如上的sql。只运行几秒，如下的查询就报错了。

SQL>  declare
  2   cursor c is
  3   select /*+ first_rows */ object_name
  4      from t
  5   order by object_id;
  6
  7   l_object_name t.object_name%type;
  8   l_rowcnt number := 0;
  9   begin
 10      open c;
 11      loop
 12              fetch c into l_object_name;
 13              exit when c%notfound;
 14              dbms_lock.sleep( 0.01);
 15              l_rowcnt := l_rowcnt + 1;
 16      end loop;
 17      close c;
 18   exception
 19      when others then
 20              dbms_output.put_line( 'rows fetched = ' || l_rowcnt);
 21              raise;
 22   end;
 23  /
 declare
*
第 1 行出现错误:
ORA-01555: 快照过旧: 回退段号 18 (名称为 "_SYSSMU18_3188057130$") 过小
ORA-06512: 在 line 21

此时，查看下undo空间，依然2M。

SQL>    select bytes/1024/1024
 2 from dba_data_files
 3 where tablespace_name = 'UNDO_SMALL';

BYTES/1024/1024 ---------------
 2

解决错误

这个长时间运行的进程需要大约718秒完成。（表中有71817条记录，0.01秒运行一条记录，共需720秒。）我的undo_retention设置为900秒（所以undo大约保持15分钟）。
查看undo_retention

SQL> show parameter undo_retention

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     900

修改undo表空间的数据文件，使之一次扩大1MB，知道最大达到2GB：

SQL> alter database
  2  datafile '/tmp/undo.dbf'
  3  autoextend on
  4  next 1m
  5  maxsize 2048m;

数据库已更改。

再次并发的运行这些进程，两个进程都能顺利完成。这一次undo表空间的数据文件扩大了，因为，再次允许的undo表空间扩大。

SQL> select bytes/1024/1024
 2 from dba_data_files
 3 where tablespace_name = 'UNDO_SMALL';

BYTES/1024/1024 ---------------
 21

本文参考《Oracle_Database_9i10g11g编程艺术深入数据库体系结构》第2版，Thomas Kyte著，苏金国 王小振等译，301~306页。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!