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

心已赠人 2022-07-18 04:21 351阅读 0赞

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

## 引起这个错误的原因有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:\Users\Administrator>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:\Users\Administrator>set oracle_home
    ORACLE_HOME=E:\app\Administrator\product\11.2.0\dbhome_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页。