OS環境：windows2008

數據庫版本：oracle 11.2.0

今天同事需要執行一個擁有大批量運算的存儲過程，當執行的時候報錯，報錯信息如下：

ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 18 with name
"_SYSSMU18_671080725$" too small
ORA-06512: at "TRANUSER.TRAN_ETL_LOAD_J2S_MAIN", line 22
ORA-06512: at "TRANUSER.TRAN_ETL_LOAD_JST_PRE", line 5
ORA-06512: at line 2

--精彩解釋

不知道是從哪里轉的了, 假設有張表，叫table1，里面有5000萬行數據，假設預計全表掃描1次需要1個小時，我們從過程來看： 

1、在1點鐘，有個用戶A發出了select * from table1;此時不管將來table1怎么變化，正確的結果應該是用戶A會看到在1點鐘這個時刻的內容。這個是沒有疑問的。 
2、在1點30分，有個用戶B執行了update命令，更新了table1表中的第4000萬行的這條記錄，這時，用戶A的全表掃描還沒有到達第4000萬條。毫無疑問，這個時候，第4000萬行的這條記錄是被寫到了回滾段里去了的，我假設是回滾段RBS1，如果用戶A的全表掃描到達了第4000萬行，是應該會正確的從回滾段RBS1中讀取出1點鐘時刻的內容的。 
3、這時，用戶B將他剛才做的操作commit了，但是這時，系統仍然可以給用戶A提供正確的數據，因為那第4000萬行記錄的內容仍然還在回滾段RBS1里，系統可以根據SCN來到回滾段里找到正確的數據，但是大家注意到，這時記錄在RBS1里的第4000萬行記錄已經發生了一點重大的改變：就是這個第4000萬行的在回滾段RBS1里的數據有可能隨時被覆蓋掉，因為這條記錄已經被提交了！！！ 
4、由于用戶A的查詢時間漫長，而業務在一直不斷的進行，RBS1回滾段在被多個不同的tracnsaction使用著，這個回滾段里的extent循環到了第4000萬行數據所在的extent，由于這條記錄已經被標記提交了，所以這個extent是可以被其他transaction覆蓋掉的！ 
5、到了1點40分，用戶A的查詢終于到了第4000萬行，而這時已經出現了第4條說的情況，需要到回滾段RBS1去找數據，但是已經被覆蓋掉了，于是01555就出現了。

--錯誤提示

數據庫報錯 ORA-01555 什么回滾段 '_SYSSMU168' is too small.很明顯 是可用的回滾段太小了 滿足不了那個大事物的需要 具體的sql我就不提供了

還有一種可能,一般伴隨著ORA-22924出現就是LOB上的問題

辨別ORA-01555是不是發生在LOB上的,一般來說,普通的01555錯誤會指明發生01555的rollback segment,而LOB的則沒有,而是伴隨著ORA-22924出現http://www.dbafan.com/blog/?p=11

--回滾原理

回退段中存放的信息被稱為“前照”(pre-image)，也就是說當一個進程對某個表進行了DML操作以后，
更改前的紀錄信息被存放于回滾段，其作用有兩個：

1、當進程要求回滾（ROLLBACK）的時候，使用回滾段中信息是紀錄復原；

2、保持數據讀的一致性，當一個進程從某個表中讀紀錄的時候，ORACLE返回的是當讀開始或者進程開始時的紀錄，如果在讀取過程中有其他進程更改了表紀錄，ORACLE就會從回滾段中讀取當讀操作開始時的數據。回滾段中信息并不是持久有效的，當進程提交（COMMIT）或者回滾（ROLLBACK）的時候，回滾段就被釋放了。當一個進程在執行一個大查詢的時候，如果在查詢的過程中所讀取得的表被更改而且更改COMMIT太久，那回滾段中的“前照”就有可能會被其他的進程覆蓋，從而導致ORA-01555錯誤。

--解決方法

1、增加回滾段的大小，因為ORACLE總是覆蓋最舊的回滾段，所以大的回滾段能有效的降低數據被覆蓋的可能性。
2、檢查你的程序，避免在一個大查詢的過程中對所查詢的表執行太多更新操作。

下面回顧下關于ora-01555的解決方法 10g默認是使用AUM 這里就不說了. 下面是幾個解決方式來自hellodba 總結的很不錯 大家可用參考下：

1、擴大回滾段: 因為回滾段是循環使用的，如果回滾段足夠大，那么那些被提交的數據信息就能保存足夠長的時間是那些大事務完成一致性讀取。

2、增加undo_retention時間:在undo_retention規定的時間內，任何其他事務都不能覆蓋這些數據。

3、優化相關查詢語句，減少一致性讀:減少查詢語句的一致性讀，就降低讀取不到回滾段數據的風險。這一點非常重要！

4、減少不必要的事務提交:提交的事務越少，產生的回滾段信息就越少。

5、對大事務指定回滾段,通過以下語句可以指定事務的回滾段：SET TRANSACTION USE ROLLBACK SEGMENT rollback_segment; 給大事務指定回滾段，即降低大事務回滾信息覆蓋其他事務的回滾信息的幾率，又降低了他自身的回滾信息被覆蓋的幾率。大事務的存在，往往是1555錯誤產生的誘因。

6、使用游標時盡量使用顯式游標，并且只在需要的時候打開游標，同時將所有可以在游標外做的操作從游標循環中拿出。當游標打開時，查詢就開始了，直到游標關閉。減少游標的打開時間，就減少了1555錯誤發生的幾率。http://hi.baidu.com/xu521huan/blog/item/0903ec9b62d85ebec8eaf442.html

--一些實例

我的回答是先看看到底是哪個SQL有這個問題，再確定不是因為SQL本身太糟糕導致SNAPSHOT TOO OLD。再跟他們說我不相信把UNDO_RETENTION加大會有效地解決問題。最后給幾個CASES來支持我的觀點。
(1)reduce the frequency of commit
(2)set initialization paramter undo_retention(9i)
(3)alter system set retention guarrantee (10g)
(4)increase the size of the undo tablespace
(5)assign a large rollback segment for the large transaction
(6)tuning the long run sql
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
the root cause of the error ora-01555:the long run query can not find a consistent image, because the undo blocks that used to construct the consistent image were wrapped by other active transaction.

遇到這個問題，首先可以看是維護需要執行的SQL或者應用執行的SQL報的

1、如果平時不報，只是維護人員執行的SQL報的，一般是SQL寫得不好，運行執行過長，超過了參數 redo_retention所設置的時間造成的。這種情況可以協助他們進行SQL分析和優化，減少運行時間，這個情況下系統不需要對系統進行調整

2、如果是應用程序報的，比如批量程序，則需要通知相關人員進行重做，否則批量運行失敗，業務可能會因為數據遺漏出現問題。如果出現的頻率較多，則需要在優化應用程序（優化的手段有SQL優化、適當增加commit的次數等）。在應用新版本上線前，可通過調整系統配置臨時解決問題方法如：

1）增大undo表空間

2）增大redo_retention

3)為此大事物指定專門的undo 段

http://www.itpub.net/viewthread.php?tid=1021888&extra=&highlight=DBA%C3%E6%CA%D4&page=3

新鮮出爐的案例：APPS的人下午回饋說今天一個DB的JOB一直報SNAPSHOT TOO OLD。這是過去幾個月這個數據庫第一次有這種回饋。到ALERT LOG中看看，有好多這種ERROR：Wed Jul 16 10:30:44 2008 ORA-01555 caused by SQL statement below (Query Duration=884 sec, SCN: 0x0018.bef62785):Wed Jul 16 10:30:44 2008

Wed Jul 16 10:57:29 2008 ORA-01555 caused by SQL statement below (Query Duration=149 sec, SCN: 0x0018.bf0d3e47):Wed Jul 16 10:57:29 2008

嗯，884S，149S，不可能吧？看看UNDO SETTINGS，很大啊：

SQL> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS2

SQL> select sum(bytes)/(1024*1024*1024) as gbytes from dba_data_files;
    GBYTES
----------
300.654297

SQL> select sum(bytes)/(1024*1024*1024) as gbytes from dba_data_files where tablespace_name='UNDOTBS2';
    GBYTES
----------
  9.765625

自己試試：
create table mytab as <the select statement> where 1=0
16:12:14 SQL> insert into mytab <the select statement>
insert into mytab
            *

ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 27 with name "_SYSSMU27$"
too small
Elapsed: 00:10:08.83
奇怪了。看看今天這個UNTOTBS2 UTILIZATION怎樣。

SQL> select snap_time, free_mb from tbs_usage_hist where database='<DB Name>' and tbs='UNDOTBS2' and snap_time>sysdate-1 order by snap_time;
SNAP_TIME              FREE_MB
------------------- ----------
2008-07-15 18:00:00    9172.56
2008-07-15 19:00:00    9172.56
2008-07-15 20:00:00    9156.56
2008-07-15 21:00:00    9188.56
2008-07-15 22:00:00    9204.56
2008-07-15 23:00:00    9212.56
2008-07-16 00:00:00    9228.56
2008-07-16 01:00:00    9228.56
2008-07-16 02:00:00    9236.56
2008-07-16 03:00:00    9228.56
2008-07-16 04:00:00    9252.56
2008-07-16 05:00:00    9252.56
2008-07-16 06:00:00    9252.56
2008-07-16 07:00:00    9260.56
2008-07-16 08:00:00    9244.56
2008-07-16 09:00:00    8486.56
2008-07-16 10:00:00    1683.56
2008-07-16 11:00:00       2.31
2008-07-16 12:00:00       1.94
2008-07-16 13:00:00       2.44
2008-07-16 14:00:00       2.44
2008-07-16 15:00:00       1.25
2008-07-16 16:00:00      17.75

那 問題應當是很明了了，自今天十點多UNDOTBS2一直是HIGHLY UTILIZED。打個電話給APP OWNER，原來他今天早上十點左右做了一個很大的DELETE。即然這個報錯的APP只要在二十四小時內能再執行完就可以，而OLTP APP沒報錯，那就再等等吧。在四點半時，UNDOTBS2就差不多是85% FREE。再試試：
16:37:49 SQL> insert into mytab <the select statement>
182 rows created.
Elapsed: 00:34:47.39
17:12:37 SQL>
現在的UNDOTBS2 UTILIZATION：
SNAP_TIME              FREE_MB
------------------- ----------
2008-07-16 17:00:00    8523.63
問題解決。SNAPSHOT TOO OLD從來就不是一個過時的題目,也沒有一個簡單的答案。