フラッシュバックトランザクション問い合わせで過去の操作を確認・復元する方法

Oracle Master Gold
2025.03.29

Oracleデータベースでは、過去のトランザクション履歴を調べるために FLASHBACK_TRANSACTION_QUERY ビューを使用することができます。
このビューを活用することで、誤って実行してしまった更新や削除操作を確認・復元することが可能です。

本記事では、UNDO_SQL情報も取得できるように最小サプリメンタルロギング（Minimal Supplemental Logging）を有効化する手順から、トランザクションの調査・復元手順までを丁寧に解説します。

💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？

Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？

✅ この記事で学べること

  • UNDO_SQL を含めた FLASHBACK_TRANSACTION_QUERY の使い方
  • 最小補足ロギング（Supplemental Logging）の設定方法
  • SCNベースでのトランザクション追跡
  • 時間指定での確認方法（補足）

🔧 1. 最小サプリメンタルロギングの有効化手順（UNDO_SQLの出力に必要）

FLASHBACK_TRANSACTION_QUERY の UNDO_SQL 情報を正しく取得するには、サプリメンタルログの出力（Supplemental Logging）が必要です。以下の手順で設定を行いましょう。

-- データベースのサプリメンタルロギング状態を確認
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

-- 有効化されていない場合、有効化
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- 状態確認（"YES" になっていれば有効）
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

この設定はインスタンス再起動不要で即時反映されます。以降に実行されるトランザクションに対して UNDO_SQL が記録されます。

🧪 2. 検証用の表を作成し、初期データを挿入

次に、動作確認用の表を作成して初期データを入れておきます。

-- テーブル作成
CREATE TABLE test_flashback (
  id NUMBER PRIMARY KEY,
  name VARCHAR2(100)
);

-- 初期データ挿入
INSERT INTO test_flashback VALUES (1, 'Apple');
INSERT INTO test_flashback VALUES (2, 'Banana');
COMMIT;

⏱ 3. SCN（System Change Number）を確認する

操作の前後に SCN を確認しておくことで、あとからそのトランザクションを特定しやすくなります。

-- 現在のSCNを取得
SELECT CURRENT_SCN FROM V$DATABASE;

この値をメモしておきましょう。

🔁 4. データ更新・削除操作を実行

次に、実際にデータを変更してみましょう。

-- データ更新
UPDATE test_flashback SET name = 'Avocado' WHERE id = 1;
COMMIT;

-- データ削除
DELETE FROM test_flashback WHERE id = 2;
COMMIT;

各操作後に SELECT CURRENT_SCN FROM V$DATABASE; を実行し、SCNを控えておくと、あとで絞り込みがしやすくなります。

🔍 5. トランザクション履歴を確認するSQL

以下のSQLを実行することで、過去のトランザクションとその復元用SQL（UNDO_SQL）を確認できます。

SELECT 
  XID,
  START_SCN,
  COMMIT_SCN,
  OPERATION,
  TABLE_NAME,
  LOGON_USER,
  UNDO_SQL
FROM FLASHBACK_TRANSACTION_QUERY
WHERE TABLE_NAME = 'TEST_FLASHBACK'
ORDER BY START_SCN DESC;

📖 各カラムの説明

カラム名説明
XIDトランザクション識別子
START_SCNトランザクション開始時のSCN
COMMIT_SCNトランザクションコミット時のSCN
OPERATION操作の種類（UPDATE, DELETEなど）
TABLE_NAME対象となった表名
LOGON_USER実行したユーザー名
UNDO_SQL元に戻すためのSQL文（復元に利用）

♻️ 6. UNDO_SQLを使ってデータを復元する

削除されたレコードを復元する場合、UNDO_SQL に表示された INSERT 文をそのまま使えば復元可能です。

-- 例：削除されたレコードの復元
INSERT INTO "TEST_FLASHBACK"("ID","NAME") VALUES ('2','Banana');

操作ミスのリカバリとして非常に有効です。

SQL> SELECT supplemental_log_data_min FROM v$database;

SUPPLEMENTAL_LOG_DATA_MI
------------------------
NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;  ★最小サプリメンタルロギングを設定

データベースが変更されました。

SQL> SELECT supplemental_log_data_min FROM v$database;

SUPPLEMENTAL_LOG_DATA_MI
------------------------
YES

SQL> alter system switch logfile;

システムが変更されました。

SQL> CREATE TABLE test_flashback (
  2    id NUMBER PRIMARY KEY,
  3    name VARCHAR2(100)
  4  );

表が作成されました。

SQL> INSERT INTO test_flashback VALUES (1, 'Apple');

1行が作成されました。

SQL> INSERT INTO test_flashback VALUES (2, 'Banana');

1行が作成されました。

SQL> col name for a30
SQL> SELECT * FROM test_flashback;

        ID NAME
---------- ------------------------------
         1 Apple
         2 Banana

SQL> commit;

コミットが完了しました。

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
    2298362

SQL> UPDATE test_flashback SET name = 'Avocado' WHERE id = 1;  ★更新

1行が更新されました。

SQL> SELECT * FROM test_flashback;

        ID NAME
---------- ------------------------------
         1 Avocado
         2 Banana

SQL> commit;

コミットが完了しました。

SQL> DELETE FROM test_flashback WHERE id = 2;  ★削除

1行が削除されました。

SQL> SELECT * FROM test_flashback;

        ID NAME
---------- ------------------------------
         1 Avocado

SQL> commit;

コミットが完了しました。

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
    2298475

SQL> set linesize 120
SQL> col operation for a20
SQL> col undo_sql for a100
SQL> col table_name for a20
SQL> col logon_user for a20
SQL> SELECT
  2    XID,
  3    START_SCN,
  4    COMMIT_SCN,
  5    OPERATION,
  6    TABLE_NAME,
  7    LOGON_USER,
  8    UNDO_SQL
  9  FROM FLASHBACK_TRANSACTION_QUERY
 10  WHERE TABLE_NAME = 'TEST_FLASHBACK'
 11  ORDER BY START_SCN DESC;

XID               START_SCN COMMIT_SCN OPERATION            TABLE_NAME           LOGON_USER
---------------- ---------- ---------- -------------------- -------------------- --------------------
UNDO_SQL
----------------------------------------------------------------------------------------------------
06001800F2030000    2298428    2298460 DELETE               TEST_FLASHBACK       TEST1
insert into "TEST1"."TEST_FLASHBACK"("ID","NAME") values ('2','Banana');  ★

07002000F1030000    2298377    2298408 UPDATE               TEST_FLASHBACK       TEST1
update "TEST1"."TEST_FLASHBACK" set "NAME" = 'Apple' where ROWID = 'AAASJTAAHAAAAGGAAA';  ★

0800160017040000    2298274    2298341 INSERT               TEST_FLASHBACK       TEST1
delete from "TEST1"."TEST_FLASHBACK" where ROWID = 'AAASJTAAHAAAAGGAAB';

0800160017040000    2298274    2298341 INSERT               TEST_FLASHBACK       TEST1
delete from "TEST1"."TEST_FLASHBACK" where ROWID = 'AAASJTAAHAAAAGGAAA';

SQL> insert into "TEST1"."TEST_FLASHBACK"("ID","NAME") values ('2','Banana');  ★

1行が作成されました。

SQL> update "TEST1"."TEST_FLASHBACK" set "NAME" = 'Apple' where ROWID = 'AAASJTAAHAAAAGGAAA';  ★

1行が更新されました。

SQL> SELECT * FROM test_flashback;  ★元の状態に戻った

        ID NAME
---------- ------------------------------
         1 Apple
         2 Banana

SQL> commit;

コミットが完了しました。

💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？

Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？

📚 7. SCN指定でデータのバージョンを確認する（FLASHBACK VERSION QUERY）

トランザクションの状態ではなく、データの変化そのものをSCNで確認したい場合は、以下の構文を使います。

SELECT 
  VERSIONS_STARTSCN,
  VERSIONS_ENDSCN,
  VERSIONS_OPERATION,
  id,
  name
FROM test_flashback
VERSIONS BETWEEN SCN 1000000 AND 1100000; -- 適切なSCNを指定

⌛ 8. 【補足】時間を指定してデータの変化を追跡

SCNではなく、日時で指定してデータの変化を確認したい場合は、以下のようにTIMESTAMPを使用します。

SELECT 
  VERSIONS_STARTTIME,
  VERSIONS_ENDTIME,
  VERSIONS_OPERATION,
  id,
  name
FROM test_flashback
VERSIONS BETWEEN TIMESTAMP
  TO_TIMESTAMP('2025-03-22 14:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND
  TO_TIMESTAMP('2025-03-22 15:00:00', 'YYYY-MM-DD HH24:MI:SS');

⚠️ 9. UNDO保持期間に注意

FLASHBACK_TRANSACTION_QUERYやVERSIONS句の情報は、UNDO領域に情報が存在している間しか取得できません。

-- UNDOの保持時間を確認
SHOW PARAMETER undo_retention;

デフォルトでは600秒（10分）程度のため、それを過ぎると履歴が参照できなくなる場合があります。

🧠 10. トランザクションの流れを図で理解

      SCN:1000000       SCN:1000100       SCN:1000200
────────────┬────────────┬──────────────▶ 時間の流れ
            │            │
        初期データ   UPDATE実行      DELETE実行
            │            │
        └─ FLASHBACK_TRANSACTION_QUERYで履歴確認可能 ─┘
              └── UNDO_SQLで復元も可能

✅ まとめ

項目内容
補足ロギングの設定ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
確認ビューFLASHBACK_TRANSACTION_QUERY
主なカラムXID, START_SCN, COMMIT_SCN, UNDO_SQL など
復元方法UNDO_SQL のSQLを実行
SCNでの確認VERSIONS BETWEEN SCN 句
時間での確認VERSIONS BETWEEN TIMESTAMP 句
注意点UNDO_RETENTION の影響を受ける

📝 おわりに

フラッシュバックトランザクション問い合わせは、過去の操作履歴を調べ、復元まで行える強力な機能です。
特に開発やテスト中の事故対応、ユーザー操作ミスの調査などで重宝します。

その力を最大限に活用するには、事前に最小サプリメンタルロギングを設定しておくことがポイントです。

[参考]
バックアップおよびリカバリ・ユーザーズ・ガイド

Oracle初心者向け：フラッシュバック・データベースの構成手順と注意事項
Oracle Databaseには、ユーザーの操作ミスや不慮のデータ破損時に、過去の時点にデータベース全体を巻き戻すことができる「フラッシュバック・データベース」機能があります。本記事では、そのフラッシュバック・データベースを使うための構成...
oracle-master00000.com
2025.04.03
Oracle フラッシュバックテクノロジーとは？
〜誤操作からの救世主、Oracleの強力な巻き戻し機能を解説〜Oracle Databaseには「フラッシュバックテクノロジー（Flashback Technology）」という強力な機能があります。これは、ユーザーが誤ってデータを削除した...
oracle-master00000.com
2025.03.26

💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？

Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？

コメント

タイトルとURLをコピーしました