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執行，提升速度