【Oracle】Flashback Data Archive完全ガイド｜Total Recall で長期変更履歴を保存して AS OF TIMESTAMP で過去データを参照する方法まで解説

-- Flashback Archive を作成する（DBA 権限が必要）
CREATE FLASHBACK ARCHIVE fba_2years
    TABLESPACE users_tbs        -- アーカイブデータを格納する表領域
    QUOTA 10G                   -- 使用する最大ストレージ（自動パージで維持）
    RETENTION 2 YEAR;           -- 履歴の保存期間（YEAR / MONTH / DAY で指定）

-- QUOTA と RETENTION を変更する
ALTER FLASHBACK ARCHIVE fba_2years
    ADD TABLESPACE users_tbs2 QUOTA 5G;

ALTER FLASHBACK ARCHIVE fba_2years
    MODIFY RETENTION 3 YEAR;

-- デフォルト Flashback Archive を設定する（テーブル指定を省略したときに使われる）
ALTER FLASHBACK ARCHIVE fba_2years SET DEFAULT;

-- Flashback Archive の一覧を確認する
SELECT
    flashback_archive_name,
    flashback_archive#,
    retention_in_days,
    status    -- DEFAULT または NULL
FROM DBA_FLASHBACK_ARCHIVE
ORDER BY flashback_archive_name;

-- 表領域の使用状況を確認する
SELECT
    tablespace_name,
    quota_in_mb,
    flashback_size
FROM DBA_FLASHBACK_ARCHIVE_TS
WHERE flashback_archive_name = 'FBA_2YEARS';

-- テーブルを Flashback Archive に登録する（アーカイブ名を指定）
ALTER TABLE hr.employees FLASHBACK ARCHIVE fba_2years;

-- デフォルト Flashback Archive に登録する（アーカイブ名を省略）
ALTER TABLE hr.orders FLASHBACK ARCHIVE;

-- Flashback Archive を無効化する（履歴は削除されない）
ALTER TABLE hr.employees NO FLASHBACK ARCHIVE;

-- 登録済みテーブルを確認する
SELECT
    owner_name,
    table_name,
    flashback_archive_name,
    status
FROM DBA_FLASHBACK_ARCHIVE_TABLES
ORDER BY owner_name, table_name;
-- STATUS: ENABLED / DISABLED

-- 1 年前の時点でのテーブルの状態を参照する
SELECT employee_id, last_name, salary, department_id
FROM hr.employees
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' YEAR)
ORDER BY employee_id;

-- 特定の日時を指定する（TIMESTAMP リテラル）
SELECT *
FROM hr.employees
AS OF TIMESTAMP TIMESTAMP '2025-01-01 00:00:00'
WHERE department_id = 50;

-- AS OF SCN（SCN 番号）で指定する（より精密な時点指定）
-- 現在の SCN を取得する
SELECT CURRENT_SCN FROM V$DATABASE;

-- 特定の SCN 時点のデータを参照する
SELECT *
FROM hr.orders
AS OF SCN 98765432
WHERE order_id = 1001;

-- 特定の employee_id の変更履歴をすべて取得する
SELECT
    versions_starttime  AS start_time,    -- このバージョンが有効になった時刻
    versions_endtime    AS end_time,      -- このバージョンが終了した時刻（NULL = 現在のバージョン）
    versions_operation  AS operation,     -- I: INSERT / U: UPDATE / D: DELETE
    versions_xid        AS transaction_id, -- トランザクション ID
    employee_id,
    last_name,
    salary
FROM hr.employees
VERSIONS BETWEEN TIMESTAMP
    TIMESTAMP '2025-01-01 00:00:00'
    AND SYSTIMESTAMP
WHERE employee_id = 101
ORDER BY versions_starttime;

-- 削除されたレコードの履歴を取得する
-- VERSIONS BETWEEN では DELETE されたレコードも operation='D' として現れる
SELECT
    versions_starttime,
    versions_endtime,
    versions_operation,
    employee_id,
    last_name
FROM hr.employees
VERSIONS BETWEEN TIMESTAMP
    SYSTIMESTAMP - INTERVAL '30' DAY
    AND SYSTIMESTAMP
WHERE employee_id = 999   -- 削除済みの employee_id でも履歴が取れる
ORDER BY versions_starttime;

-- 特定の時点以前の古い履歴を手動でパージする（DBA 権限が必要）
ALTER FLASHBACK ARCHIVE fba_2years
    PURGE BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL '18' MONTH);

-- 特定テーブルの Flashback Archive データのみパージする
ALTER FLASHBACK ARCHIVE fba_2years
    PURGE FOR TABLE hr.employees;

-- Flashback Archive 全体を削除する（登録テーブルの NO FLASHBACK ARCHIVE を先に実行）
-- テーブルの登録を解除してから削除する
ALTER TABLE hr.employees NO FLASHBACK ARCHIVE;
DROP FLASHBACK ARCHIVE fba_2years;