Oracle加索引的时候快照过久,【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:\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

@H_374_301@解决错误

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

查看undo_retention

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页。