【Oracle】ORA-01555: スナップショットが古すぎます 完全ガイド｜発生メカニズム・UNDO_RETENTION・V$UNDOSTAT・対処法・fetch-across-commitまで解説

ORA-01555: スナップショットが古すぎます:
         ロールバック・セグメント番号 5（名前 "_SYSSMU5_3748562394$"）は小さすぎます
-- または:
ORA-01555: snapshot too old: rollback segment number 5
           with name "_SYSSMU5_3748562394$" is too small

-- V$UNDOSTAT: 直近の UNDO 統計情報（10分間隔で最大 576 行 = 4日分）
SELECT
    TO_CHAR(BEGIN_TIME, 'YYYY/MM/DD HH24:MI') AS begin_time,
    TO_CHAR(END_TIME,   'YYYY/MM/DD HH24:MI') AS end_time,
    UNDOBLKS,          -- 消費した UNDO ブロック数
    TXNCOUNT,          -- トランザクション数
    MAXQUERYLEN,       -- この期間の最長クエリ実行時間（秒）
    MAXCONCURRENCY,    -- 最大同時トランザクション数
    SSOLDERRCNT,       -- ORA-01555 の発生回数 ← ここを確認
    NOSPACEERRCNT      -- UNDO 容量不足エラー数
FROM V$UNDOSTAT
ORDER BY BEGIN_TIME DESC
FETCH FIRST 24 ROWS ONLY;   -- 直近4時間分
-- SSOLDERRCNT > 0 の行があれば ORA-01555 が発生している

-- UNDO_RETENTION の現在値（秒）を確認
SELECT NAME, VALUE FROM V$PARAMETER
WHERE NAME = 'undo_retention';
-- デフォルト: 900（15分）

-- UNDO 表領域の使用状況
SELECT
    t.tablespace_name,
    t.status,
    ROUND(SUM(d.bytes) / 1024 / 1024 / 1024, 2)           AS total_gb,
    ROUND(SUM(CASE WHEN d.autoextensible = 'YES'
                   THEN d.maxbytes ELSE d.bytes END)
          / 1024 / 1024 / 1024, 2)                         AS max_gb,
    t.retention                                             -- NOGUARANTEE / GUARANTEE
FROM DBA_TABLESPACES t
JOIN DBA_DATA_FILES d ON t.tablespace_name = d.tablespace_name
WHERE t.contents = 'UNDO'
GROUP BY t.tablespace_name, t.status, t.retention;

-- 使用中の UNDO セグメント
SELECT USN, STATUS, RSSIZE/1024/1024 AS rssize_mb, WRITES, XACTS
FROM V$ROLLSTAT
ORDER BY RSSIZE DESC;

-- DBA_HIST_UNDOSTAT: AWR が保存した過去の UNDO 統計（デフォルト8日保存）
SELECT
    TO_CHAR(BEGIN_TIME, 'YYYY/MM/DD HH24:MI') AS begin_time,
    MAXQUERYLEN,       -- 最長クエリ時間（秒）
    SSOLDERRCNT,       -- ORA-01555 発生回数
    UNDOBLKS,
    TXNCOUNT
FROM DBA_HIST_UNDOSTAT
WHERE SSOLDERRCNT > 0  -- ORA-01555 が発生した期間のみ
ORDER BY BEGIN_TIME DESC;

-- UNDO_RETENTION を 1 時間（3600秒）に変更
ALTER SYSTEM SET UNDO_RETENTION = 3600 SCOPE = BOTH;
-- SCOPE = BOTH: 即時反映 + spfile に保存

-- 夜間バッチが 4 時間かかる場合は 4 時間以上に設定
ALTER SYSTEM SET UNDO_RETENTION = 14400;   -- 4時間

-- 確認
SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'undo_retention';

-- 注意: UNDO 表領域が小さすぎると RETENTION GUARANTEE がない場合に
-- 設定値より短い保持時間になることがある（次の対処法参照）

-- UNDO 表領域に RETENTION GUARANTEE を設定
ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE;

-- 解除する場合
ALTER TABLESPACE UNDOTBS1 RETENTION NOGUARANTEE;

-- 現在の設定確認（RETENTION 列が GUARANTEE または NOGUARANTEE）
SELECT TABLESPACE_NAME, RETENTION
FROM DBA_TABLESPACES
WHERE CONTENTS = 'UNDO';

-- 注意: GUARANTEE を設定する前に UNDO 表領域のサイズが十分かを確認すること
-- 不足した場合は ORA-30036 が発生して DML が失敗する

-- UNDO 生成速度を計算する
SELECT
    AVG(UNDOBLKS) * 8192 / 600   AS undo_bytes_per_sec,   -- 8192 = ブロックサイズ（DB_BLOCK_SIZEで確認）
    (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'undo_retention') AS retention_sec,
    AVG(UNDOBLKS) * 8192 / 600
        * (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'undo_retention')
        / 1024 / 1024 / 1024                               AS recommended_gb
        -- ※ 8192 は標準ブロックサイズ。実際の値は:
        -- SELECT VALUE FROM V$PARAMETER WHERE NAME='db_block_size'
FROM V$UNDOSTAT
WHERE BEGIN_TIME >= SYSDATE - 7;   -- 直近7日間の平均

-- 結果例: recommended_gb = 5.2 → 余裕を持って 10GB 以上を確保

-- UNDO 表領域にデータファイルを追加して拡張する
ALTER TABLESPACE UNDOTBS1
ADD DATAFILE '/oradata/ORCL/undotbs02.dbf' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 30G;

-- BAD: カーソル FETCH 中に COMMIT している
DECLARE
    CURSOR c IS SELECT employee_id, salary FROM employees;
    v_id   employees.employee_id%TYPE;
    v_sal  employees.salary%TYPE;
BEGIN
    OPEN c;
    LOOP
        FETCH c INTO v_id, v_sal;
        EXIT WHEN c%NOTFOUND;

        UPDATE employees
        SET salary = v_sal * 1.1
        WHERE employee_id = v_id;

        COMMIT;   -- ← ここで COMMIT すると、カーソルが必要とする UNDO が解放可能になる
                  -- → 次の FETCH 時に ORA-01555 が発生する可能性がある
    END LOOP;
    CLOSE c;
END;
/

-- GOOD: 先にすべてのデータを取得してから更新する
DECLARE
    TYPE t_emp_ids  IS TABLE OF employees.employee_id%TYPE INDEX BY PLS_INTEGER;
    TYPE t_salaries IS TABLE OF employees.salary%TYPE      INDEX BY PLS_INTEGER;
    l_ids  t_emp_ids;
    l_sals t_salaries;
BEGIN
    -- 先にデータを全件取得（カーソルをクローズしてから DML）
    SELECT employee_id, salary
    BULK COLLECT INTO l_ids, l_sals
    FROM employees;

    -- 取得後にまとめて更新（COMMIT 時にカーソルは使っていない）
    FOR i IN 1 .. l_ids.COUNT LOOP
        UPDATE employees
        SET salary = l_sals(i) * 1.1
        WHERE employee_id = l_ids(i);
    END LOOP;

    COMMIT;   -- FETCH は終わっているので ORA-01555 は発生しない
END;
/
-- 大量データで一括取得が難しい場合は LIMIT 句で分割処理する

-- UNDO 関連パラメータを一括確認
SELECT NAME, VALUE, DESCRIPTION
FROM V$PARAMETER
WHERE NAME IN (
    'undo_management',    -- AUTO（推奨）または MANUAL
    'undo_tablespace',    -- 使用中の UNDO 表領域名
    'undo_retention'      -- 保持秒数
)
ORDER BY NAME;

-- 直近7日間で最も長かったクエリ実行時間（秒）
-- → UNDO_RETENTION の設定目安になる
SELECT MAX(MAXQUERYLEN) AS max_query_sec
FROM DBA_HIST_UNDOSTAT
WHERE BEGIN_TIME >= SYSDATE - 7;