OS環(huán)境：windows2008

數(shù)據(jù)庫版本：oracle 11.2.0

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

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

--精彩解釋

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

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

--錯誤提示

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

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

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

--回滾原理

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

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

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

--解決方法

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

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

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

2、增加undo_retention時間:在undo_retention規(guī)定的時間內(nèi)，任何其他事務都不能覆蓋這些數(shù)據(jù)。

3、優(yōu)化相關(guān)查詢語句，減少一致性讀:減少查詢語句的一致性讀，就降低讀取不到回滾段數(shù)據(jù)的風險。這一點非常重要！

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

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

6、使用游標時盡量使用顯式游標，并且只在需要的時候打開游標，同時將所有可以在游標外做的操作從游標循環(huán)中拿出。當游標打開時，查詢就開始了，直到游標關(guān)閉。減少游標的打開時間，就減少了1555錯誤發(fā)生的幾率。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.

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

1、如果平時不報，只是維護人員執(zhí)行的SQL報的，一般是SQL寫得不好，運行執(zhí)行過長，超過了參數(shù) redo_retention所設置的時間造成的。這種情況可以協(xié)助他們進行SQL分析和優(yōu)化，減少運行時間，這個情況下系統(tǒng)不需要對系統(tǒng)進行調(diào)整

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

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。這是過去幾個月這個數(shù)據(jù)庫第一次有這種回饋。到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只要在二十四小時內(nèi)能再執(zhí)行完就可以，而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>
現(xiàn)在的UNDOTBS2 UTILIZATION：
SNAP_TIME              FREE_MB
------------------- ----------
2008-07-16 17:00:00    8523.63
問題解決。SNAPSHOT TOO OLD從來就不是一個過時的題目,也沒有一個簡單的答案。