Oracle ORA-01555 :：snapshot too old Error Message

標籤： Oracle

ORA-01555 Error Message

 

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說明：