ORA-01555经典错误

4r221

--创建undo表空间时固定表空间的大小

sys@TDB112>createundo tablespace undo_small

  2 datafile'/u01/app/oracle/oradata/TDB112/undo_small.dbf'

  3  size2m

  4 autoextend off;

--切换表空间为定义的小的undo表空间  

sys@TDB112>altersystem set undo_tablespace=undo_small;

--创建测试表，并且要求数据是随机产生的

sys@TDB112>createtable t

  2  as

  3 select * from all_objects

  4 order by dbms_random.random;

--创建一个主键约束，目的是创建一个索引：

sys@TDB112>altertable t add constraint t_pk primary key(object_id);

--收集表的统计信息，目的是让优化器使用索引：

sys@TDB112>execdbms_stats.gather_table_stats( user, 'T', cascade=> true );

--大量更新操作

sys@TDB112>begin

  2  forx in ( select rowid rid from t )

  3  loop

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

  5 commit;

  6  endloop;

  7  end;

  8  /

--

在运行这个修改的同时，我们在另一个会话中运行一个查询。这个查询要读表T，并处理每个记录。

获取下一个记录之前处理每个记录所花的时间大约为1/100秒（使用DBMS_LOCK.SLEEP(0.01)来模拟）。在

查询中使用了FIRST_ROWS提示，使之使用前面创建的索引，从而通过索引（按OBJECT_ID 排序）来读出表

中的行。由于数据是随机地插入到表中的，我们可能会相当随机地查询表中的块。这个查询只运行几秒就

会失败：

sys@TDB112>declare

  2 cursor c is

  3 select /*+ first_rows */ object_name

  4  fromt

  5 order by object_id;

  6 l_object_name t.object_name%type;

  7 l_rowcnt number := 0;

  8 begin

  9  openc;

10  loop

11 fetch c into l_object_name;

12  exitwhen c%notfound;

13 dbms_lock.sleep( 0.1 );

14 l_rowcnt := l_rowcnt+1;

15  endloop;

16 close c;

17 exception

18  whenothers then

19 dbms_output.put_line( 'rows fetched = ' || l_rowcnt );

20 raise;

21  end;

22  /

declare

*

ERROR at line 1:

ORA-01555: snapshottoo old: rollback segment number 14 with name "_SYSSMU14_2266994445$"too small

ORA-06512: at line20

报错分析：

1、undo表空间太小；

2、因为在更新是一行一行按照原来插入数据的顺序地更新的，减少了对每个指定行更新前的查找记录的时间；相反，对于上面的查询来说，

因为不是按照原来插入数据的顺序查找数据的，而是按照自己object_id排好序的顺序查找数据的。所以下面的查询语句必定比上面的更

新语句执行的慢，所需要的时间也比较的长。这样就有可能发生这样的情况：当查询语句需要查一行数据时，但是该行数据已经被更新语

句在很久以前就已经被更新修改过并且已经提交掉，因为UNDO表空间很小，又由于更新采用的是循环更新操作，使得UNDO表空间被循环利用，

在循环中后面的更新语句所产生的UNDO数据会覆盖掉之前产生的UNDO数据，造成此时查询语句查找不到在查找开始的时间点上该行记录的

数据，则会报ORA-01555。

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

对上面出现的问题进行解决需要做两件事情：

1、对UNDO_RETENTION参数需要设置的大点，设置为允许读数据进程全部完成所需要的最长时间；

2、在调大UNDO_RETENTION参数值同时，需要将undo_tablespace的大小也需要设置的大点；

   如果是自动管理UNDO表空间，则设置为自动增长；

   如果是手动管理UNDO表空间则分配更多的磁盘空间给UNDO表空间

对于上面的例子：

1）对于上面的查询需要的时间小于900s,执行时间大概为780s，通过

sys@TDB112>showparameter undo_retention

NAME                                 TYPE        VALUE

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

undo_retention                       integer     900

查询UNDO_RETENTION原来的值为900s，UNDO_RETENTION参数值是足够的，所以先不需要修改；

2）由于原来的UNDO表空间为2M，是不可以自动增长的，所以设置UNDO表空间为自动增长，以1M的速度增长，并且最大设置为2G；

sys@TDB112>selectfile_name

  2  fromdba_data_files

  3 where tablespace_name='UNDO_SMALL';

FILE_NAME

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

/u01/app/oracle/oradata/TDB112/undo_small.dbf

sys@TDB112>alter database datafile'/u01/app/oracle/oradata/TDB112/undo_small.dbf' autoextend on next 1m maxsize2048m;

做了上面的修改操作之后，下面来重新同时执行上面的更新语句和查询语句，就不会有ORA-01555错误产生；

11g官方文档叙述：

ORA-01555: snapshot too old: rollback segment number string with name "string" too small

Cause: rollback recordsneeded by a reader for consistent read are overwritten by other writers

Action: If in Automatic UndoManagement mode, increase undo_retention setting. Otherwise, use largerrollback segments