ORA-01555: 使用事務刪除時快照太舊錯誤

我希望有人能解釋我為什麼會看到 ORA-01555。我有一個函式和一個程序來在一個巨大的表中執行清理：

-- Small cleanup in separate transaction.
FUNCTION clean_single(ts_until IN TIMESTAMP, datapoint_id IN NUMBER) RETURN NUMBER IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   DELETE FROM VALUES WHERE DATAPOINT_ID = datapoint_id AND TS < ts_until;
   COMMIT;
   RETURN sql%rowcount;
END clean_single;

PROCEDURE prc_clean IS
   count_all_deleted_vals NUMBER(10) := 0;
BEGIN
   BEGIN
       FOR dps IN (
           SELECT x AS dpid, y as tsUntil FROM Z where some conditions
           LOOP
               count_all_deleted_vals := count_all_deleted_vals + clean_single(dps.tsUntil, dps.dpid);
           END LOOP;
       DBMS_OUTPUT.PUT_LINE('Removed ' || count_all_deleted_vals || ' values');
   END;
END prc_clean;

這個想法是prc_clean()從一個作業中執行，在選擇了相關的數據點 ID 後，每個數據點 ID 的刪除在單個事務中完成，以避免產生一個巨大的事務。

但是當我執行它時，它會執行一段時間，然後因 ORA-01555 而失敗。詳細地說，我不明白為什麼會這樣。為什麼PRAGMA AUTONOMOUS_TRANSACTION;函式中的 in 不能阻止這種情況？我能做些什麼來防止它？

ORA-01555 很可能來自 FOR 中的 SELECT。

即使一個 DELETE 是一個單獨的事務並且它可能會很快完成，但 SELECT 的游標必須在整個執行過程中保持打開狀態，並且它總是必須從它開始的時間點返回結果。如果Z在執行時被修改，則從執行開始時重建所需的撤消Z可能會被覆蓋 => ORA-01555。

編寫這樣的循環是一個非常常見的錯誤。

更好的方法是將 的結果儲存SELECT x AS dpid, y as tsUntil FROM Z where some conditions在 PL/SQL 集合中，然後在循環中遍歷該集合，而不是在循環中遍歷查詢。

每個 Oracle 事務都在它開始時執行。

為了實現這一點，Oracle 有效地將事務中使用的每個數據塊“閃回”到初始 SCN。為了執行這些“即時閃回”，Oracle 需要Undo Tablespace中的空間。一旦它發現它不能“返回”足夠遠的數據塊，你就會得到 ORA-01555。

除非 datapoint_id 是唯一的，否則刪除將不得不掃描多行（對每一行執行“即時閃回”），從而增加 Oracle 必須做的工作並增加“用完”的風險"的撤消來支持它。

雖然它有自己的風險（即行移動），但一種替代方法可能是辨識要刪除的每一行的 ROWID 並將其傳遞給clean_single 函式。這樣，刪除只會考慮那一行。

引用自：https://dba.stackexchange.com/questions/315344