【Oracle】Flashback Version Query・Flashback Transaction Query完全ガイド｜VERSIONS BETWEEN で行レベルの変更履歴を追跡する方法まで解説

-- VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE: 取得可能な全期間の変更履歴を返す
SELECT
    VERSIONS_XID          AS xid,                  -- トランザクション ID
    VERSIONS_OPERATION    AS operation,             -- I=INSERT, U=UPDATE, D=DELETE
    VERSIONS_STARTSCN     AS start_scn,             -- この版の開始 SCN
    VERSIONS_ENDSCN       AS end_scn,               -- この版の終了 SCN（NULL=現在の版）
    VERSIONS_STARTTIME    AS start_time,            -- この版の開始日時（近似）
    VERSIONS_ENDTIME      AS end_time,              -- この版の終了日時
    employee_id, last_name, salary                  -- テーブルの実データ
FROM   employees VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE  employee_id = 107
ORDER  BY VERSIONS_STARTSCN NULLS FIRST;

-- VERSIONS_OPERATION の値:
-- I: この版は INSERT によって作られた（行が新規追加された）
-- U: この版は UPDATE によって作られた（行が変更された）
-- D: この版は DELETE によって作られた（行が削除された）
-- NULL: 現在の版（最新データ）

-- TIMESTAMP ベースで過去2時間の変更履歴を取得する
SELECT
    VERSIONS_STARTTIME, VERSIONS_OPERATION, employee_id, last_name, salary
FROM   employees VERSIONS BETWEEN TIMESTAMP
         (SYSTIMESTAMP - INTERVAL '2' HOUR)
       AND
         SYSTIMESTAMP
WHERE  employee_id = 107
ORDER  BY VERSIONS_STARTTIME;

-- SCN ベースで特定の SCN 範囲を指定する
SELECT
    VERSIONS_STARTSCN, VERSIONS_OPERATION, employee_id, salary
FROM   employees VERSIONS BETWEEN SCN 5000000 AND 5100000
WHERE  department_id = 60
ORDER  BY VERSIONS_STARTSCN;

-- FLASHBACK_TRANSACTION_QUERY の権限: SELECT ANY TRANSACTION または FLASHBACK ANY TABLE が必要

-- XID を指定してそのトランザクションの詳細を確認する
SELECT
    xid,
    operation,          -- INSERT / UPDATE / DELETE / UNKNOWN
    start_timestamp,    -- トランザクション開始日時
    commit_timestamp,   -- COMMIT 日時
    logon_user,         -- 変更を行ったユーザー
    undo_sql,           -- 変更を元に戻すための SQL（UNDO SQL）
    table_name,
    table_owner
FROM   FLASHBACK_TRANSACTION_QUERY
WHERE  xid = HEXTORAW('01000A0058060000')  -- VERSIONS_XID の値
ORDER  BY undo_change#;

-- VERSIONS BETWEEN と FLASHBACK_TRANSACTION_QUERY を結合して確認する
SELECT
    v.VERSIONS_STARTTIME,
    v.VERSIONS_OPERATION,
    v.employee_id,
    v.last_name,
    v.salary,
    f.logon_user,        -- 変更を行ったユーザー
    f.undo_sql           -- 変更を元に戻す SQL
FROM   employees VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE v
JOIN   FLASHBACK_TRANSACTION_QUERY f ON v.VERSIONS_XID = f.xid
WHERE  v.employee_id = 107
  AND  v.VERSIONS_OPERATION IS NOT NULL    -- 現在の版を除く
ORDER  BY v.VERSIONS_STARTSCN;

-- UNDO SQL を使って特定の変更を手動で元に戻す例
-- undo_sql: 'UPDATE "HR"."EMPLOYEES" SET SALARY=5000 WHERE ROWID=''AAABkUAAEAAAAC7AAA'';'
-- → undo_sql を実行すれば変更前の状態に戻せる

-- 現在の UNDO_RETENTION を確認する（秒単位）
SHOW PARAMETER undo_retention;
-- undo_retention = 900 (デフォルト): 最低 900秒（15分）UNDO を保持する保証

-- より長い履歴を参照するには UNDO_RETENTION を増やす
ALTER SYSTEM SET UNDO_RETENTION = 86400;  -- 24時間（86400秒）保持

-- undo_retention はガイドラインであり、UNDO 表領域が満杯の場合は
-- 古い UNDO から上書きされる（GUARANTEE なし）
-- UNDO 表領域に RETENTION GUARANTEE を設定すると期限前上書きを防止できる
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

-- 現在参照可能な SCN の範囲を確認する
SELECT oldest_flashback_scn, oldest_flashback_time
FROM   V$FLASHBACK_DATABASE_LOG;
-- oldest_flashback_scn より前の SCN は参照できない（UNDO が消えている可能性）

-- ORA-01555 (snapshot too old) が出たら UNDO が不足している
-- VERSIONS BETWEEN で ORA-01555 が出る場合は UNDO_RETENTION を増やすか
-- UNDO 表領域を拡張する