ora-01555

産生ORA-01555錯誤主要有三種可能性：

1、UNDO段對于系統所運作的工作負荷太小；

2、在循環過程中有事務送出是造成ORA-01555錯誤的最常見的原因；

3、block cleanout；

解決ORA-01555錯誤的解決方案有下面幾種：

1、适當的設定UNDO_RETENTION參數值（大于事務執行可能需要的最長時間）；如果UNDO_RETENTION參數值變大，那麼同時也需要根據果UNDO_RETENTION參數值大小修改UNDO表

   空間的大小也需要做相應的修改；（适合于UNDO管理是自動管理）

   網上查詢UNDO表空間的大小可以根據公式：

   Undo Size = Undo_retention * UPS

   UPS是undo block per second, 我們可以通過V$UNDOSTAT. UNDOBLKS獲得       .

   SQL> select avg(undoblks)/(10*60) UPS from v$undostat;

   UPS 

   -------------

   0.03

   則undo_retention=10800，至少需要  10800*0.03=324個資料塊。

2、如果是手動管理UNDO，增長UNDO段的大小或者增加UNDO段的的個數，這樣做可以減少在查詢延續的時間比較長的情況下UNDO資料被覆寫的可能性。這個解決方案可以解決上面

   的三種造成錯誤原因的情況；

3、減少查詢時間，優化查詢語句。如果可能的話，這是最好的方法，是以這是首要考慮的方法。因為減少查詢時間可以減少對UNDO空間的需求。這個解決方案可以解決上面

4、對相關的對象做下分析統計。因為這個方法會避免上面造成錯誤的第三種可能性：block cleanout的情況發生。因為block cleanout的情況經常是大量的更新或者插入操作

   的結果。是以在進行完大量的更新或者插入操作之後做下相關對象的分析統計是有必要的。

實驗1：針對第二種最常見的情況和第一種可能情況：

--建立undo表空間時固定表空間的大小

create undo tablespace undo_small 

 datafile 'undo_small .dbf' 

 size 2m 

 autoextend off ；

--切換表空間為定義的小的undo表空間   

alter system set undo_tablespace=undo_small;

drop table t

--建立測試表，并且要求資料是随機産生的

create table t

as

select *

from all_objects

order by dbms_random.random;

alter table t add constraint t_pk primary  key(object_id);

--統計表

begin

dbms_stats.gather_table_stats('DAIMIN','T',cascade=>true);

end;

--大量更新操作

begin 

 for x in ( select rowid rid from t ) 

 loop 

 update t set object_name = lower(object_name) where rowid = x.rid; 

 commit; 

 end loop; 

 end;  

(注：采用循環更新送出比一次性更新送出要慢很多，即送出的次數多反而性能慢。即上面的更新語句比update t set object_name = lower(object_name)要慢很多。

 有興趣的可以測試下，這個不是這裡主要講的。)

--在上一個程式執行的同時，下面的語句在另一個會話中執行，則會報ORA-01555錯誤

declare 

 cursor c is   

 select /*+ first_rows */ object_name    --c遊标中的資料集是按照object_id排好序的。

 from t 

 order by object_id; 

l_object_name t.object_name%type; 

 l_rowcnt number := 0; 

 begin 

 open c; 

fetch c into l_object_name; 

exit when c%notfound; 

dbms_lock.sleep( 0.01 ); 

l_rowcnt := l_rowcnt+1; 

end loop; 

 close c;

 exception

 when others then

 dbms_output.put_line( 'rows fetched = ' || l_rowcnt );

  raise;

 end;

報錯分析：

1、undo表空間太小；

2、因為在更新是一行一行按照原來插入資料的順序地更新的，減少了對每個指定行更新前的查找記錄的時間；相反，對于上面的查詢來說，

因為不是按照原來插入資料的順序查找資料的，而是按照自己object_id排好序的順序查找資料的。是以下面的查詢語句必定比上面的更

新語句執行的慢，所需要的時間也比較的長。這樣就有可能發生這樣的情況：當查詢語句需要查一行資料時，但是該行資料已經被更新語

句在很久以前就已經被更新修改過并且已經送出掉，因為UNDO表空間很小，又由于更新采用的是循環更新操作，使得UNDO表空間被循環利用，

在循環中後面的更新語句所産生的UNDO資料會覆寫掉之前産生的UNDO資料，造成此時查詢語句查找不到在查找開始的時間點上該行記錄的

資料，則會報ORA-01555。 

-----------------------------------------------------------------------------------------------------

對上面出現的問題進行解決需要做兩件事情：

1、對UNDO_RETENTION參數需要設定的大點，設定為允許讀資料程序全部完成所需要的最長時間；

2、在調大UNDO_RETENTION參數值同時，需要将undo_tablespace的大小也需要設定的大點；

   如果是自動管理UNDO表空間，則設定為自動增長；

   如果是手動管理UNDO表空間則配置設定更多的磁盤空間給UNDO表空間

對于上面的例子：

1）對于上面的查詢需要的時間小于900s,執行時間大概為780s，通過

  SQL> show parameter UNDO_RETENTION

  NAME                                 TYPE        VALUE 

  ----------------------------------- ----------- ------------------------------

  undo_retention                       integer     900

查詢UNDO_RETENTION原來的值為900s，UNDO_RETENTION參數值是足夠的，是以先不需要修改；

2）由于原來的UNDO表空間為2M，是不可以自動增長的，是以設定UNDO表空間為自動增長，以1M的速度增長，并且最大設定為2G；

SQL> select file_name

  2   from dba_data_files

  3   where tablespace_name = 'UNDO_SMALL';

FILE_NAME

--------------------------------------------------------------------------------

C:/ORACLE/PRODUCT/10.2.0/DB_1/DATABASE/UNDO_SMALL .DBF

alter database 

 datafile 'C:/ORACLE/PRODUCT/10.2.0/DB_1/DATABASE/UNDO_SMALL .DBF' 

 autoextend on 

 next 1m 

 maxsize 2048m;  

 做了上面的修改操作之後，下面來重新同時執行上面的更新語句和查詢語句，就不會有ORA-01555錯誤産生；

在AUM模式下，我們知道UNDO_RETENTION參數用以控制事務送出以後undo資訊保留的時間。該參數以秒為機關，9iR1初始值為900秒，在Oracle9iR2增加為10800秒。但是這是一個NO Guaranteed的限制。

也就是說，如果有其他事務需要復原空間，而空間出現不足時，這些資訊仍然會被覆寫。

很多時候這是不希望看到的。

從Oracle10g開始，如果你設定UNDO_RETENTION為0，那麼Oracle啟用自動調整以滿足最長運作查詢的需要。當然如果空間不足，那麼Oracle滿足最大允許的長時間查詢。而不再需要使用者手工調整。

同時Oracle增加了Guarantee控制，也就是說，你可以指定UNDO表空間必須滿足UNDO_RETENTION的限制。

在Oracle官方文檔上這樣解釋:

RETENTION Undo tablespace retention: 

GUARANTEE - Tablespace is an undo tablespace with RETENTION specified as GUARANTEE

A RETENTION value of GUARANTEE indicates that unexpired undo in all undo segments in the undo tablespace should be retained even if it means that forward going operations that need to generate undo in those segments fail.

本文轉自einyboy部落格園部落格，原文連結：http://www.cnblogs.com/einyboy/archive/2012/06/11/2544779.html