【Oracle】SCN（System Change Number）完全ガイド｜V$DATABASE・SCN ⇔ TIMESTAMP 変換・Flashback との関係まで解説

-- 方法①: V$DATABASE で現在の SCN を確認する（DBA権限不要）
SELECT current_scn FROM V$DATABASE;
-- 例: 15234567890

-- 方法②: DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER（12c 以降は非推奨だが依然動作する）
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;

-- 方法③: V$DATABASE のその他 SCN 関連情報
SELECT
    current_scn,              -- 現在の SCN（最新のコミット SCN）
    checkpoint_change#,       -- 最後のチェックポイント SCN
    resetlogs_change#,        -- 最後の RESETLOGS 時の SCN（インカネーション境界）
    archive_change#           -- 最後にアーカイブされたログの SCN
FROM V$DATABASE;

-- 各セッションのトランザクション開始 SCN を確認する
SELECT
    s.sid,
    s.username,
    t.start_scn        -- そのトランザクションの開始 SCN（V$TRANSACTION から）
FROM V$SESSION s
JOIN V$TRANSACTION t ON s.taddr = t.addr
WHERE s.username IS NOT NULL;

-- SCN → タイムスタンプ（SCN が発生した時刻を取得する）
SELECT SCN_TO_TIMESTAMP(15234567890) FROM DUAL;
-- 例: 09-APR-26 10:30:00.000000000 AM +09:00

-- タイムスタンプ → SCN（時刻から直近の SCN を取得する）
SELECT TIMESTAMP_TO_SCN(TIMESTAMP '2026-04-09 10:00:00') FROM DUAL;
-- 例: 15234512345

-- 現在から1時間前の SCN を取得する
SELECT TIMESTAMP_TO_SCN(SYSTIMESTAMP - INTERVAL '1' HOUR) AS scn_1hr_ago FROM DUAL;

-- SCN を使って過去のデータを参照する（Flashback Query）
SELECT *
FROM employees AS OF SCN 15234512345
WHERE department_id = 60;
-- TIMESTAMP_TO_SCN で取得した SCN を指定することで正確な時点のデータを参照できる

-- 注意: SMON_SCN_TIME の保持期間は UNDO_RETENTION に依存する
-- 古すぎる SCN は ORA-08181 (specified number is not a valid system change number) になる
SELECT SCN_TO_TIMESTAMP(1) FROM DUAL;
-- ORA-08181: specified number is not a valid system change number（古い SCN は変換できない）

-- 古すぎないか確認する（最古の SCN を確認する）
SELECT oldest_flashback_scn, oldest_flashback_time
FROM V$FLASHBACK_DATABASE_LOG;
-- oldest_flashback_scn より前は SCN_TO_TIMESTAMP が失敗する可能性がある

-- セッション A: 長時間かかる SELECT を実行（開始 SCN: 15000000）
-- セッション B: SELECT 実行中に多数の UPDATE をコミット（SCN が増加）
-- → セッション B のコミットにより、UNDO に必要な古いバージョンが上書きされる
-- → セッション A が古い SCN のデータを読もうとすると UNDO が不足して ORA-01555 発生

-- UNDO の保持状況を確認する
SELECT
    tuned_undoretention,   -- 実際に保持されている UNDO の期間（秒）
    maxquerylen,           -- この時間窓内の最長クエリ時間（秒）
    undoblks,              -- 使用された UNDO ブロック数
    ssolderrcnt            -- ORA-01555 の発生回数
FROM V$UNDOSTAT
ORDER BY end_time DESC
FETCH FIRST 12 ROWS ONLY;  -- 直近 12 行（1時間分）

-- UNDO_RETENTION の設定を確認する
SHOW PARAMETER undo_retention;  -- デフォルト: 900 秒（15分）

-- ORA-01555 が多発する場合の対処
-- ① UNDO_RETENTION を増やす
ALTER SYSTEM SET UNDO_RETENTION = 3600;   -- 1時間

-- ② UNDO 表領域に RETENTION GUARANTEE を設定する（UNDO の早期上書きを防止）
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

-- ③ 長時間クエリを小分けにする（ROWNUM や ROWID でバッチ処理）
-- ④ セッション設定で UNDO の影響を最小化する

-- 現在の SCN とヘッドルームを計算する
SELECT
    current_scn,
    -- SCN の理論上限: データベース作成からの経過秒 × 16384
    (EXTRACT(DAY FROM (SYSTIMESTAMP - TO_TIMESTAMP('01-JAN-1988', 'DD-MON-YYYY'))) * 86400
    + EXTRACT(HOUR FROM (SYSTIMESTAMP - TO_TIMESTAMP('01-JAN-1988', 'DD-MON-YYYY'))) * 3600
    + EXTRACT(MINUTE FROM (SYSTIMESTAMP - TO_TIMESTAMP('01-JAN-1988', 'DD-MON-YYYY'))) * 60
    + EXTRACT(SECOND FROM (SYSTIMESTAMP - TO_TIMESTAMP('01-JAN-1988', 'DD-MON-YYYY')))
    ) * 16384 AS max_scn,
    ROUND(
        ((EXTRACT(DAY FROM (SYSTIMESTAMP - TO_TIMESTAMP('01-JAN-1988', 'DD-MON-YYYY'))) * 86400) * 16384 - current_scn)
        / (16384 * 3600 * 24 * 365), 1
    ) AS headroom_years   -- 残りの理論的ヘッドルーム（年単位）
FROM V$DATABASE;

-- Oracle 12.2 以降: V$DATABASE に SCN_TO_TIME_FORMAT 等の追加列がある
-- SCN_TO_TIME_FORMAT が CONVERSION_TO_LOCAL_TIME_ENABLED なら精度が高い

-- SCN ヘッドルームが少ない場合（通常は何十年も余裕があるが、バグやミスで急増した場合）:
-- ① バグ（Bug 13498243 など）による SCN スパイクを確認する
-- ② SCN 上限が近い場合はサポートに連絡してパッチを適用する

-- データベースの SCN 増加速度を確認する（直近の統計から推定）
SELECT
    100 AS sample_count,
    AVG(end_scn - begin_scn) AS avg_scn_per_interval,
    MAX(end_scn - begin_scn) AS max_scn_per_interval
FROM (
    SELECT
        current_scn AS end_scn,
        LAG(current_scn, 1, current_scn) OVER (ORDER BY 1) AS begin_scn
    FROM V$DATABASE
    CONNECT BY LEVEL <= 1   -- 単一行取得のためのダミー
);
-- 実際の増加速度を確認するには時間をおいて複数回クエリする