【Oracle】フラッシュバックの使い方完全ガイド｜AS OF TIMESTAMP・Flashback Table・Flashback Drop・誤操作からのデータ復旧

-- 30分前の時点の employees テーブルを参照
SELECT employee_id, name, salary
FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '30' MINUTE)
WHERE department_id = 10;

-- 2026年3月29日 14:00:00 時点のデータを参照
SELECT employee_id, name, salary
FROM employees AS OF TIMESTAMP
    TO_TIMESTAMP('2026-03-29 14:00:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE department_id = 10;

SELECT CURRENT_SCN FROM V$DATABASE;
-- 例: 12345678

-- 操作前に控えておいたSCNを使って復旧
SELECT employee_id, name, salary
FROM employees AS OF SCN 12345678
WHERE department_id = 10;

-- ステップ 1: 削除前のSCNがわかっている場合
-- まず、復旧対象のデータを確認する
SELECT * FROM employees AS OF SCN 12345678
WHERE department_id = 10;

-- ステップ 2: 確認できたら INSERT で復元する
INSERT INTO employees (employee_id, name, salary, department_id)
SELECT employee_id, name, salary, department_id
FROM employees AS OF SCN 12345678
WHERE department_id = 10
  AND employee_id NOT IN (SELECT employee_id FROM employees);  -- 既存行との重複を排除

COMMIT;

-- 「全社員の salary を誤って 0 に UPDATE してしまった」場合の復旧
MERGE INTO employees e
USING (
    SELECT employee_id, salary
    FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR)
) old ON (e.employee_id = old.employee_id)
WHEN MATCHED THEN
    UPDATE SET e.salary = old.salary;

COMMIT;

SELECT
    employee_id,
    name,
    salary,
    VERSIONS_STARTTIME  AS changed_at,     -- この版が有効になった時刻
    VERSIONS_ENDTIME    AS replaced_at,    -- この版が次の版に置き換わった時刻（NULLなら現在有効）
    VERSIONS_OPERATION  AS op,             -- I=INSERT, U=UPDATE, D=DELETE
    VERSIONS_XID        AS transaction_id  -- トランザクションID
FROM employees
    VERSIONS BETWEEN TIMESTAMP
        TO_TIMESTAMP('2026-03-29 09:00:00', 'YYYY-MM-DD HH24:MI:SS')
    AND SYSTIMESTAMP
WHERE employee_id = 1001
ORDER BY VERSIONS_STARTTIME;

-- SCN 範囲を指定して変更履歴を取得する
SELECT
    employee_id,
    name,
    salary,
    VERSIONS_STARTSCN,
    VERSIONS_ENDSCN,
    VERSIONS_OPERATION AS op
FROM employees
    VERSIONS BETWEEN SCN 12345000 AND 12346000
WHERE employee_id = 1001
ORDER BY VERSIONS_STARTSCN;

ALTER TABLE employees ENABLE ROW MOVEMENT;
-- これにより Oracle は物理的な ROWID を変更できるようになる
-- Flashback Table 完了後に無効に戻すことも可能

FLASHBACK TABLE employees
    TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '30' MINUTE);

FLASHBACK TABLE employees
    TO SCN 12345678;

-- ごみ箱に入っているオブジェクト一覧
SELECT
    object_name,          -- ごみ箱内での名前（BIN$...形式）
    original_name,        -- 元のテーブル名
    type,                 -- TABLE / INDEX 等
    droptime,             -- DROP された日時
    can_undrop            -- YES=復元可能, NO=不可
FROM user_recyclebin
ORDER BY droptime DESC;

-- 元のテーブル名で復元する
FLASHBACK TABLE employees TO BEFORE DROP;

-- テーブル名が既に別のテーブルに使われている場合、リネームして復元
FLASHBACK TABLE employees TO BEFORE DROP RENAME TO employees_restored;

-- 特定テーブルをごみ箱から完全削除（復元不可になる）
PURGE TABLE employees;

-- 自分のごみ箱を全て空にする
PURGE RECYCLEBIN;

-- DBA権限で全ユーザーのごみ箱を空にする
PURGE DBA_RECYCLEBIN;

-- DROP時にごみ箱を経由せず即座に完全削除する
DROP TABLE employees PURGE;

-- 最小補足ログを有効にする（DBA権限が必要）
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- 確認
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
-- YES なら有効

-- VERSIONS_XID で取得したトランザクションIDを使って詳細を調べる
SELECT
    xid,
    operation,       -- INSERT / UPDATE / DELETE
    table_name,
    undo_sql         -- この変更を取り消すためのSQL文
FROM flashback_transaction_query
WHERE xid = HEXTORAW('0007001C00000123');  -- VERSIONS_XID の値を指定

-- 現在の UNDO 保持期間を確認する（秒数）
SHOW PARAMETER undo_retention;
-- 例: 900（15分）= デフォルト

-- UNDO 保持期間を1時間に延長する
ALTER SYSTEM SET undo_retention = 3600 SCOPE=BOTH;

-- UNDO 表領域の保証（RETENTION GUARANTEE）を有効にする
-- これにより UNDO_RETENTION 以内のデータは上書きされなくなる
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

-- UNDO 表領域のサイズと使用率を確認
SELECT
    tablespace_name,
    ROUND(SUM(bytes) / 1024 / 1024) AS total_mb,
    ROUND(SUM(CASE WHEN status = 'ACTIVE'    THEN bytes ELSE 0 END) / 1024 / 1024) AS active_mb,
    ROUND(SUM(CASE WHEN status = 'UNEXPIRED' THEN bytes ELSE 0 END) / 1024 / 1024) AS unexpired_mb,
    ROUND(SUM(CASE WHEN status = 'EXPIRED'   THEN bytes ELSE 0 END) / 1024 / 1024) AS expired_mb
FROM dba_undo_extents
GROUP BY tablespace_name;