ORA-01555錯誤詳解

一：‍
‍在電信行業這種資料量巨大的環境中，ora-01555錯是一個很常見的錯誤。這個錯誤使得應用失敗。例如，這一錯誤可能停止一個在深夜啟動並執行批處理任務，隨後也使依賴於該任務的其他任務失敗。這使使用者不能及時得到所需的資訊(如報表沒列印出來、資料未被匯出等等)。儘管這一錯誤通常發生在大任務上，但在小任務上也會發生。
　　ORA-1555通常是一個偶然出現的錯誤。有時在發生了該錯誤以後，重新運行該任務就有可能不再碰到類似的錯誤。這個錯誤最麻煩的是它並不會立刻發生，已耗用時間長的任務在錯誤失敗以前可能已經運行了一段時間了(可能幾個小時)。只是簡單地重新運行該任務並不能保證它能成功，可能在運行了一段時間以後仍然失敗。
　　1 原因分析
　　ORA-1555錯的根本原因是因為Oracle要保證讀一致性。讀一致性是指當有多個使用者對一個資料區塊內的行進行修改時，這些塊變“髒”或處於變化之中直到被確認。在被確認以前，它們對事務中的所有語句都是可見的，但是對別的事務或語句而言是不可見的。一旦確認以後，對所有後繼的事務或語句就都是可見的了。但在事務被確認前的語句不能看到修改，因為這些修改還未發生。
　　例如，事務T 1(如對某大表的exp操作)在2 2 ：0 0開始而事務T 2(如對同一大表的update操作)在2 2 ：0 1時開始，因為T 1需遍曆一個很大的表，其讀取要花很長的時間，而T 2可能對同一個表中的資料進行基於索引的更新操作。這樣， T2可能在幾秒鐘之內完成，而T 1可能要運行很長時間，假定4 0分鐘。當T 1到達T 2做過修改的地方時(根據當前的S C N時間戳記可以識別出新作的改變)，儘管T 2所進行的寫已經被確認，但為了保證讀一致性,它不會讀到修改後的資料,它只訪問在2 2 ：0 0時的資料，在2 2 ：0 1時所做的改變不能被讀取 。T 1從復原段中讀取改變前的資料以保證讀一致性。但因為事務T2已經提交，T2事務使用的復原段oracle認為已經可以重新利用，當復原段太少或事務較密集時，oracle有可能會用新事務覆蓋掉原來T2事務的復原段，這時T1事務讀到被T2修改過的資料時，再從復原段中就無法找到修改前的資料，這時就會報ORA-1555,snapshot too old錯。
　　下面我們可以結合執行個體來將此過程回溯一遍：
　　(1)事務T1在22點開始執行了對某一個大表Test1的exp操作(Test1表資料量可能有幾千萬甚至更多)，那麼按照經驗，此操作可能需要執行40分鐘左右或更長;
　　(2)事務T2在22點01分開始執行對Test1表某行的update操作，並且操作條件上有索引(將col1為00的行，col2值由90修改為100)，故此操作很快完成，比如5秒鐘完成操作並commit;
　　(3)此時事務T2已經執行完畢，而事務T1還在執行中;
　　(4)當事務T1需要將col1為00的行匯出為dmp檔案時，Oracle為了保證讀一致性，即T1匯出的必須是22點時資料庫表的值，故col1為00的行對於T1任務來說值仍然為90，而非100;
　　(5)由於T2事務在22點02分前就已經做完(提交)，並且T2認為復原段是可以重新利用的;
　　(6)如果此時由於復原段太少或業務量較密集，oracle就可能會重新利用剛才T2事務所使用的復原段。這時T1事務讀到此處時，就會造成無法找到復原段中修改前的資料，產生錯誤。
　　2 9i中對復原段管理
　　在9i中，可以有兩種解決方案來維護事務的讀一致性，即或者使用自Oracle 6以來就一直使用的復原段，或者是使用Undo Tablespace來進行的自動重做管理，但是這兩種方法不能同時使用。
　　考試大建議在9i 中使用復原資料表空間而不是8i 的復原段模式來管理資料庫。
　　(1)建立undotablespace
　　建立undotablespace的文法如下：
　　create undotablespace tablespace_name
　　datafile ’fullpath+datafilename’ size XXM
　　[autoextend on|off next XX maxsize XX]; 來
‍二：

寫了段java操作資料庫的代碼

Java代碼  String getIPList="select t.dns_ip from t_dnscachetotal t where t.locid=0";         
 String getLocid="select  t3.locid from (select max(t2.ipstart) ipstart,max(t2.ipend) ipend from t_GGMAP_IP t2 where t2.ipstart<=query_ip(?)) t1,t_GGMAP_IP t3 where t1.ipend>=query_ip(?) and t1.ipstart=t3.ipstart";         
String updateDnsCacheTotal="update t_dnscachetotal t set t.locid=? where t.dns_ip=?";             
stmt=con.prepareStatement(getIPList);         
 rs=pstmt.executeQuery();         
String ip;                   
 ResultSet rs2;         
 int countupdate=0;         
while(rs.next()){               
 ip=rs.getString(1);                   
pstmt2 = con.prepareStatement(getLocid);                 
pstmt2.setString(1, ip);                 
pstmt2.setString(2, ip);                 
rs2=pstmt2.executeQuery();               
 int locid=-1;                 
while(rs2.next()){                   
 locid=rs2.getInt(1);                  }
  pstmt2.close();                 
  rs2.close();                 
  countupdate++;                 
  pstmt2=con.prepareStatement(updateDnsCacheTotal);                 
  pstmt2.setInt(1, locid);                 
  pstmt2.setString(2, ip);                 
  pstmt2.executeUpdate();                 
    pstmt2.close();                               
    }         
  pstmt.close();         
  rs.close(); 
String getIPList="select t.dns_ip from t_dnscachetotal t where t.locid=0"; String getLocid="select t3.locid from (select max(t2.ipstart) ipstart,max(t2.ipend) ipend from t_GGMAP_IP t2 where t2.ipstart<=query_ip(?)) t1,t_GGMAP_IP t3 where t1.ipend>=query_ip(?) and t1.ipstart=t3.ipstart"; String updateDnsCacheTotal="update t_dnscachetotal t set t.locid=? where t.dns_ip=?"; pstmt=con.prepareStatement(getIPList); rs=pstmt.executeQuery(); String ip; ResultSet rs2; int countupdate=0; while(rs.next()){ ip=rs.getString(1); pstmt2 = con.prepareStatement(getLocid); pstmt2.setString(1, ip); pstmt2.setString(2, ip); rs2=pstmt2.executeQuery(); int locid=-1; while(rs2.next()){ locid=rs2.getInt(1); } pstmt2.close(); rs2.close(); countupdate++; pstmt2=con.prepareStatement(updateDnsCacheTotal); pstmt2.setInt(1, locid); pstmt2.setString(2, ip); pstmt2.executeUpdate(); pstmt2.close(); } pstmt.close(); rs.close();

 

大體就這樣。。我刪了一部分代碼。

核心的問題就在於

while(rs.next()){
     
    }

因為rs.next實際上是對oracle某表持續的查詢，而在迴圈中又在不斷地update這個表，從而導致了這個1555錯誤，

ora 1555別人的例子 寫道首先瞭解Oracle在什麼情況下會產生ORA-01555錯誤:

假設有一張6000萬行資料的testdb表，預計testdb全表掃描1次需要2個小時，參考過程如下：
1、在1點鐘，使用者A發出了select * from testdb;此時不管將來testdb怎麼變化，正確的結果應該是使用者A會看到在1點鐘這個時刻的內容。
2、在1點30分，使用者B執行了update命令，更新了testdb表中的第4100萬行的這條記錄，這時，使用者A的全表掃描還沒有到達第4100萬條。毫無疑問，這個時候，第4100萬行的這條記錄是被寫入了復原段，假設是復原段UNDOTS1，如果使用者A的全表掃描到達了第4100萬行，是應該會正確的從復原段UNDOTS1中讀取出1點鐘時刻的內容的。
3、這時，使用者B將他剛才做的操作提交了，但是這時，系統仍然可以給使用者A提供正確的資料，因為那第4100萬行記錄的內容仍然還在復原段UNDOTS1裡，系統可以根據SCN到復原段裡找到正確的資料，但要注意到，這時記錄在UNDOTS1裡的第4100萬行記錄已經發生了重大的改變：就是第4100萬行在復原段UNDOTS1裡的資料有可能隨時被覆蓋掉，因為這條記錄已經被提交了！
4、由於使用者A的查詢時間漫長，而業務在一直不斷的進行，UNDOTS1復原段在被多個不同的transaction使用著，這個復原段裡的extent迴圈到了第4100萬行資料所在的extent，由於這條記錄已經被標記提交了，所以這個extent是可以被其他transaction覆蓋掉的！
5、到了1點45分，使用者A的查詢終於到了第4100萬行，而這時已經出現了第4條說的情況，需要到復原段UNDOTS1去找資料，但是已經被覆蓋掉了，這時就出現了ORA-01555錯誤。
 

明顯我是犯了同樣的錯誤。而解決辦法大致有三個

首先：

SQL> show parameter undo
 
NAME                                TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                      integer    900
undo_tablespace                      string      UNDOTBS1

 

可以看到我們是自動管理undo的，(11g)，另外undo_retention時間是900s,這個是可以考慮放大的，

寫道關於初始化參數UNDO_RETENTION的設定，嚴格說起來也是與UNDO資料表空間有關係，但是思量再三，我覺著還是有必要單拎出來詳細介紹。

該參數用來指定UNDO段中���據儲存的最短時間，以秒為單位，是一個動態參數，完全可以在執行個體運行時隨時修改，通常預設是900秒，也就是15分鐘。

首先要注意，UNDO_RETENTION只是指定UNDO段中資料的到期時間，並不是說，UNDO段中的資料一定會在UNDO資料表空間中儲存15分鐘。如一個新事務開始的時候，如果此時UNDO資料表空間已經被寫滿，則新事務的資料會自動覆蓋已提交事務的資料，而不管這些資料是否已到期，因此呢，這就又關聯回了第一點，當你建立一個自動管理的UNDO資料表空間時，還要注意其空間大小，要儘可能保證UNDO資料表空間有足夠的儲存空間。

同時還要注意，也並不是說，UNDO_RETENTION中指定的時間一過，已經提交事務中的資料就立刻無法訪問，當超出UNDO_RETENTION參數指定的時間後，這部分資料佔用的空間將會被標識為可重用，不過只要不被別的事務觸發的資料覆蓋，它會仍然存在，並可以隨時被Flashback特性引用。如果你的UNDO資料表空間足夠大，而資料庫又不是那麼繁忙，那麼其實UNDO_RETENTION參數的值並不會影響到你，哪怕你設定成1（這麼說好像絕對了點，大家一定要注意理解，別鑽牛角尖），只要沒有事務去覆蓋UNDO資料，這部分資料就會持續有效。因此呢，再次重複那句話，要注意UNDO資料表空間的大小，保證其有足夠的儲存空間。

最後，只有在一種情況下，UNDO資料表空間能夠確保UNDO中的資料在UNDO_RETENTION指定時間到期前一定有效，就是為UNDO資料表空間指定RETENTION GUARANTEE，指定之後，不會覆蓋UNDO資料表空間中未到期的UNDO資料，例如：

SQL> ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE; 如果想禁止UNDO資料表空間RETENTION GUARANTEE，例如：

SQL> ALTER TABLESPACE UNDOTBS1 RETENTION NOGUARANTEE; 轉了一圈，問題又回來了，既然它看起來有用又像沒有用，為什麼還要設定它呢？嘿嘿，就我理解，其存在的真實用途，就是提醒你UNDO資料表空間很重要，給它指定分配一個合適的大小更重要喲。

 但是從這篇文章中明顯發現，undo_retention參數意義很小，而且預設的TABLESPACE UNDOTBS1 一般都是NOGUARANTEE，這個是一個考慮解決1555錯誤的方法，但是我個人覺得大部分情況絕不是因為undo_retention過小引起的，應該都是因為UNDOTBS1 佔滿了的緣故，所以

1、擴大復原段

因為復原段是迴圈使用的，如果復原段足夠大，那麼那些被提交的資料資訊就能儲存足夠長的時間是那些大事務完成一致性讀取。

2、增加undo_retention時間

在undo_retention規定的時間內，任何其他事務都不能覆蓋這些資料。

3、最佳化相關查詢語句，減少一致性讀。

減少查詢語句的一致性讀，就降低讀取不到復原段資料的風險。這一點非常重要！

4、減少不必要的事務提交

提交的事務越少，產生的復原段資訊就越少。

5、對大事務指定復原段

通過以下語句可以指定事務的復原段：

SET TRANSACTION USE ROLLBACK SEGMENTrollback_segment；

我覺得標紅的都是可以考慮的方案，

我的方案套件括了

1.while(rs.next()){
     
    }去掉，不在rs迴圈內執行update，而是把rs資料全部讀出，然後在進行逐條update,

2.無非就是標紅的那些了