Oracle ORA-01555 :：snapshot too old Error Message
ORA-01555 :：snapshot too old Error Message
標籤： Oracle
ORA-01555 Error Message
出現ORA-01555錯誤，通常有下列情況：
SQL語句執行時間太長，或者UNDO表空間過小，或者事務量過大，或者過於頻繁的提交，導致執行SQL過程中進行一致性讀時，SQL執行後修改的前鏡像（即UNDO資料）在UNDO表空間中已經被覆蓋，不能構造一致性讀塊（CR blocks）。 這種情況最多。
解決的辦法：
（1）增加UNDO表空間大小
（2）增加undo_retention 時間，默認只有15分鐘
（3）優化出錯的SQL，減少查詢的時間，首選方法
（4）避免頻繁的提交
ORA-01555 caused by SQL statement below (SQL ID: 52ssczq4j3vp6, Query Duration=0 sec, SCN: 0x0b6b.3481606d):
Mon Nov 4 15:41:03 2013
SELECT "A1"."PROD_NAME",'IN',NVL( (SELECT SUM("A5"."INIT_IN_QTY") FROM "DWC5"."MPS_DAILY" "A5" WHERE "A5"."SHIFT_DATE">='20131101' AND "A5"."SHIFT_DATE"<='20131103' AND "A5"."PROD_NAME"="A1"."PROD_NAME" AND '20131101'<='20131103'),0),NVL( (SELECT SUM("A4"."IN_QTY") FROM "DWC5"."MPS_DAILY" "A4" WHERE "A4"."MONTH"='201311' AND "A4"."PROD_NAME"="A1"."PROD_NAME"),0),NVL( (SELECT SUM("A3"."IN_QTY") FROM "DWC5"."MPS_DAILY" "A3" WHERE "A3"."SHIFT_DATE">='20131101' AND "A3"."SHIFT_DATE"<='20131103' AND "A3"."PROD_NAME"="A1"."PROD_NAME" AND '20131101'<='20131103'),0),SUM(NVL("A1"."IN_SUBS_QTY",0)-NVL("A1"."CANCEL_IN_SUBS_QTY",0)) FROM "DWC5"."PRODUCT" "A2","DWC5"."DAILY_IN_OUT_SUM" "A1" WHERE "A1"."SHOP"='C5' AND "A1"."SHIFT_DATE">='20131101' AND "A1"."SHIFT_DATE"<='20131103' AND "A1"."CUT_TIME"='070000' AND ("A1"."LOT_TYPE"='P' OR "A1"."LOT_TYPE"='E') AND "A1"."PROD_NAME"="A2"."PROD_NAME" AND ("A2"."DAILYRPT_DISPFLAG_IO"='M' OR "A2"."DAILYRPT_DISPFLAG_IO"='A') AND '20131101'<='20131103' GROUP BY "A1"."PROD_NAME"
Mon Nov 4 15:47:10 2013
在上面，有出現ORA Error、SQL ID & SQL語法：
一、 透過EM查看，上面寫說UNDO TABLE的Retention值過大(10818秒)，經查詢undo retention為10800秒(SQL> show parameter_retention)，不夠18秒，因此跳出此錯誤，更改此undo retention值。
ORA-01555 Snapshot Too Old Error detected: Undo Tablespace UNDOTBS1, Current Undo Retention 10818.
SQL> show parameter undo
NAME TYPE VALUE
———————————— ———– ——————————
undo_management string AUTO
undo_retention integer 10800
undo_tablespace string UNDOTBS1
設定undo_retention值為10818：
ALTER SYSTEM SET UNDO_RETENTION = 10818;
二、 請AP人員幫忙確認一下此SQL，是否有效率。
透過此SQL_ID，找詢相關資訊(時間若過的太久，也有可能查詢不到)
selectsql_id,first_load_time,parsing_schema_name,service,module,cpu_time,disk_reads,sql_text from v$sql WHERE sql_id=<52ssczq4j3vp6>;
透過parsing_schema_name，可以看出此User是誰，在到v$session Table查詢相關資訊(若此人的session不在了，當然也查不到了)：
selectsid,username,schemaname,osuser,machine,terminal,sql_id,logon_time,service_name, event,module from v$session WHERE sql_id=<52ssczq4j3vp6>;;
若很幸運的，兩個table都有查到資料，應該知道人是誰了吧！！
selectv.sql_id,v.first_load_time,v.module,v.cpu_time,v.disk_reads,
s.username,s.schemaname,s.osuser,s.machine,s.terminal,s.logon_time,s.service_name,s.event, v.sql_text
from v$sql v ,v$session s where v.sql_id=s.sql_id order byv.first_load_time desc;
v$sql說明：
v$sql除了 group by 不顯示，其他皆會顯示（共用sql,說白了就是shared pool 中的library cache)；另外：一些長查詢，它是每5秒更新一條統計資訊，其他的查詢sql則是每次查詢執行完就會更新統計資訊了
undo_retention說明：
undo_retention: 該參數用來指定undo 記錄保存的最長時間，以秒為單位，是個動態參數，完全可以在實例運行時隨時修改通常默認是900 秒，也就是15 分鐘。
該參數用來指定undo 記錄保存的最長時間，以秒為單位，是個動態參數，完全可以在實例運行時隨時修改通常默認是900 秒，也就是15 分鐘。
一定要注意，undo_retention 只是指定undo 數據的過期時間，並不是說，undo 中的數據一定會在undo表空間中保存15 分鐘，比如說剛一個新事務開始的時候，如果undo 表空間已經被寫滿，則新事務的數據會自動覆蓋已提交事務的數據，而不管這些數據是否已過期，因此呢，這就又關聯回了第一點，當你創建一個自動管理的undo 表空間時，還要注意其空間大小，要盡可能保證undo 表空間有足夠的存儲空間。
同時還要注意，也並不是說，undo_retention 中指定的時間一過，已經提交事務中的數據就立刻無法訪問，它只是失效，只要不被別的事務覆蓋，它會仍然存在，並可隨時被flashback 特性引用。如果你的undo表空間足夠大，而數據庫又不是那麼繁忙，那麼其實undo_retention 參數的值並不會影響到你，哪怕你設置成1，只要沒有事務去覆蓋undo 數據，它就會持續有效。因此呢，這裏還是那句話，要注意undo 表空間的大小，保證其有足夠的存儲空間。
只有在一種情況下，undo 表空間能夠確保undo 中的數據在undo_retention 指定時間過期前一定有效，就是為undo 表空間指定Retention Guarantee，指定之後，oracle 對於undo 表空間中未過期的undo 數據不會覆蓋，
例如：
SQL> Alter tablespace undotbs1 retention guarantee;
相關資料：
Undo_retention說明：
