Oracle的ORA-01555

什么是ORA-01555

 首先解释下Ora-01555。

[oracle@rhel_lky02 ~]$ oerr 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

 ORA-01555错误代码，字面意思就是快照过旧。这是Oracle数据库中很常见的一个错误，当我们的事务需要使用undo来构建CR块的时候，而此时对应的undo 已经不存在了， 这个时候就会报ORA-01555的错误。

场景模拟

 接下来用一个简单的例子模拟下这个ora-01555错误代码的出现。

 首先创建一个较小的undo表空间，且不能自动扩展，并将undo_retention设置为1秒

SQL> create undo tablespace undo_2 datafile '/u01/app/oracle/oradata/standby/undo_2.dbf' size 3M autoextend off;
Tablespace created.

SQL> alter system set undo_retention=1 scope=spfile;
System altered.

SQL> alter system set undo_tablespace=undo_2 scope=spfile;
System altered.

接下来.重启数据库。然后我们查看到undo参数如下，

SQL> show parameter undo;

NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
undo_management string AUTO
undo_retention integer 1
undo_tablespace string TEST_UNDO

然后我们开始这个错误场景的模拟。首先进行如下的准备工作，

#使用liu用户登录
SQL> connect liu/liu
Connected.
SQL> show user
USER is "LIU"

#创建一个表T4
SQL> create table t4 as select * from dba_objects where rownum<=10000;

Table created.

#定义一个游标,打开游标
SQL> var c1 refcursor
SQL> begin
  2 open :c1 for select * from liu.t4;
  3 end;
  4 /

PL/SQL procedure successfully completed.

#更新表T4的一行数据，并提交
SQL> update liu.t4 set object_id=111 where rownum<=2;

1 row updated.

SQL> commit;

Commit complete.

 而这时，游标一直处于open状态，这个select语句直到print :c1操作完成才结束。即模拟了一个执行时间较长的select。

 接下来是比较关键的部分了，模拟对undo表空间的大量占用。

#创建表T5
SQL> create table t5 as select * from dba_objects where 1=2;

Table created.
#使用一个循环，让大量的事务对表T5进行批量的修改
SQL> begin
  for i in 1..20000 loop
  insert into liu.t5 select * from dba_objects where rownum<=1000;
  delete from liu.t5;
  commit;
  end loop;
  end;
  /

此时，会有大量的redo log及undo log产生，直到把磁盘空间占满，

SQL> select event,seconds_in_wait from v$session where username='SYS';

EVENT SECONDS_IN_WAIT
---------------------------------------------------------------- ---------------
log file switch (archiving needed) 18
SQL*Net message from client 0

从上面的等待时间可以看到，日志已经不能进行正常的切换了。

[root@oracle11g ~]# df -Th|grep archive 
/dev/sdc1 ext4 9.9G 9.3G 102M 99% /archive
#清空归档，释放空间
[root@oracle11g ~]# cd /archive/ 
[root@oracle11g archive]# rm -rf *
#然后切换日志
SQL> alter system switch logfile;
System altered.
#再次查看等待事件
SQL> select event,seconds_in_wait from v$session where username='SYS';

EVENT SECONDS_IN_WAIT
---------------------------------------------------------------- ---------------
log buffer space 0
SQL*Net message from client 35
SQL*Net message to client 0
SQL*Net message from client 35

然后紧接着出现如下报错

ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'TEST_UNDO'
ORA-06512: at line 4

此时，说明undo表空间已被全部覆盖。接下来，我们打印出前面一直处于open状态的游标。

SQL> print :c1
…
ERROR:
ORA-01555: snapshot too old: rollback segment number 23 with name
"_SYSSMU23_894261744$" too small

那么，这个ora-01555错误终于出现了。造成这个报错的原因在于，undo已经全部被我们模拟的大量事务所覆盖。

简单总结

首先，会导致ora-01555错误出现的情况如下，

1.undo表空间过小且不能自动扩展
2.retention时间过小
3.事务提交频繁
4.undo数据增长过快
5.select执行时间过长

然后，我们的解决方法有以下几种，

1.加大undo表空间
2.retention时间设置大一些
3.合并事务
4.优化sql执行，提升速度