知らないなんてもったいない！Oracleのタイムマシン『フラッシュバッククエリー』活用法
ごあいさつ
こんにちは、「エイジ＠フジワーランド」です
フリーでパッケージシステムのOEM供給やってますが、最近暇なので時給でSESやってます
規則ただしく生活するのも慣れてきました、早起きもいいものです
さて今回はOracleのフラッシュバッククエリー(FLASHBACK QUERY)の話です
フラッシュバッククエリー(FLASHBACK QUERY)とは
ズバリ、過去のデータを参照できる機能です
この機能を使えばバックアップデータに依存せず過去データを確認できます
使いこなせば問題解決やデータ修復の効率アップ間違いなしです(※個人の感想です)
この機能はOracleでは特に事前準備なしで利用可能です
例えばこんな使い方：
- テスト実行時の更新してしまったデータの更新前の確認
- いつ時点でデータがおかしくなったか時間を遡ってデータ調査
- 誤って削除してしまったデータを削除前の状態を参照して復元
まるでタイムマシン！…厳密にいうとタイムテレビですかね、なんせ本当に便利です
ただ、すごい便利機能なのに認知度が低いようであまり使われていないようですね
この記事でぜひフラッシュバッククエリーを知っていただき、開発や保守の作業効率アップにぜひ役立ててほしいです
この記事でわかること
Oracleの「フラッシュバッククエリー」の基本的な使い方と実用的な使用例を解説します
Oracleには他にもテーブルを過去の状態に復元する「フラッシュバックテーブル」や「フラッシュバックデータベース」という機能もありますが、今回は参照専用の「フラッシュバッククエリー」の解説です
1. フラッシュバッククエリーの基本的な使い方
ここではフラッシュバッククエリー(FLASHBACK QUERY)の基本的な使い方を具体的なサンプルとともに解説します
1. 過去のデータを参照する
過去の特定の時点に存在したデータを取得する場合は、AS OF TIMESTAMP句を使用します
サンプル1: 特定の時点での従業員データを取得する
以下は、2024年12月20日15時00分のデータを参照するクエリです。
SELECT *
FROM employees
AS OF TIMESTAMP TO_TIMESTAMP('2024-12-20 15:00:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE department_id = 10;
このクエリでは、指定した時点でdepartment_idが10の従業員データを取得します
サンプル2: 過去の売上情報を確認する
売上データを特定の時点にさかのぼって確認します
SELECT order_id, customer_id, total_amount
FROM sales
AS OF TIMESTAMP TO_TIMESTAMP('2024-11-30 12:00:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE total_amount > 1000;
このクエリでは、2024年11月30日12時の時点で売上金額が1000を超える注文を取得します。
2. SCNを利用したデータ参照
タイムスタンプではなくSCN（System Change Number）を使用して過去のデータを参照することも可能です
SCNはタイムスタンプよりも精度の高い連番でOracle内部ではデータにこの連番が振られています
※SCNについては別記事で公開する予定です
サンプル3: SCNを使った従業員データの参照
SELECT *
FROM employees
AS OF SCN 123456789
WHERE employee_id = 101;
このクエリは、SCNが123456789の時点での従業員データを取得します。
3. 誤操作の影響範囲を確認する
誤ってデータを削除したり更新してしまった場合、その影響範囲を特定するのにFLASHBACK QUERYが役立ちます
サンプル4: 誤削除されたデータの確認
SELECT *
FROM accounts
AS OF TIMESTAMP TO_TIMESTAMP('2024-12-22 10:00:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE account_status = 'ACTIVE';
このクエリを使用すると、特定の時点でACTIVEだったアカウントを確認できます。削除後の状態と比較することで、影響範囲を把握できます
サンプル5: 誤更新されたデータの確認
SELECT employee_id, salary
FROM employees
AS OF TIMESTAMP TO_TIMESTAMP('2024-12-21 09:00:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE employee_id = 102;
このクエリでは、指定した時点での従業員の給与データを確認できます。これにより、誤って変更された内容を把握し、元の値に復元する際に役立ちます
2. フラッシュバッククエリーの実用的な使用例
例1: 過去テーブルどうしをJOINして参照する
SELECT b.secion_name, a.employee_name
FROM (employees AS OF TIMESTAMP TO_TIMESTAMP('2024-12-23 09:00:00', 'YYYY-MM-DD HH24:MI:SS')) a
INNER JOIN (sections AS OF TIMESTAMP TO_TIMESTAMP('2024-12-23 09:00:00', 'YYYY-MM-DD HH24:MI:SS')) b ON a.secion_id = b.section_id
ORDER BY b.section_id, a.employee_id
このクエリで、特定時点でのsection_nameと所属するemployee_nameを確認できます
ここでは例を出してませんが、テーブルそれぞれ時差を付けて参照することもできます
例2: 相対時間を指定する
SELECT *
FROM transactions
AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '30' MINUTE;
このクエリでは、現在時刻から30分前のtransactionsテーブルのデータを参照します
例3: 更新差分を取得する
SELECT * FROM employees AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '10' MINUTE
MINUS
SELECT * FROM employees
このクエリでは、10分以内に変更されたemployeesテーブルのデータを確認できます
3. フラッシュバッククエリー使用時の注意点
-
フラッシュバッククエリーはUNDOデータを使用して遡っている
- 遡りが可能な範囲はUNDOデータにデータが存在する間のみです、UNDOデータ容量が少なかったり大量のデータが更新された場合は過去のデータを参照できないことがあります
- フラッシュバッククエリーで参照したデータを
INSERT文でDBに保存すると過去のUNDOデータが上書きされて参照できなくことがあるので注意が必要です - 「ORA-01555: スナップショットが古すぎます: ロールバック・セグメント番号1、名前"_SYSSMU1_1542306202$"が小さすぎます」等のエラーメッセージが出る場合はUNDOデータに遡れるデータが無いときです
※ まれに TIMESTAMPの内部変換がバグってUNDOデータがあるのにこのエラーが出る時があります、その場合はSCNで指定で参照できます。詳しくは別記事を公開する予定です
- フラッシュバッククエリー前提にシステムを構築しない
- 遡れる期間が不定なため、前月対比レポートなどをこの機能で作り込むと出力できない場合があります。 システム構築はこの機能に依存せず過去データを保存する仕組みの実装をおすすめします
-
大規模データ操作時の消費資源
- データ操作が大規模な場合、UNDOデータの容量が増大しクエリレスポンス時間が増加することがあります
おわりに
なぜか認知度の低いフラッシュバッククエリー…
いざというときに役に立つと思いますので、ぜひ存在だけでも憶えていただきたいです
よければこの記事を参考にしてフラッシュバッククエリーを積極的に活用してみてください
みなさんの作業効率が少しでもあがることを願います
最後まで読んでいただきありがとうございました
Discussion