Oracle10g フラッシュバック アゲインの巻 その２
＜Oracle10g フラッシュバック アゲイン の巻 その２＞
ペンネーム：びー・うぃりー
先週に引き続きフラッシュバックに関する動作確認をしてまいります。
今回は、参照系機能の２つ目「行履歴フラッシュバック」です。
「行履歴フラッシュバック」では、変更内容を行単位で参照できます。
ある行を更新していき現在の値に至るまでの履歴が参照できるという事です。
もちろんUNDO表領域にデータが残っている事が前提ですが・・・・。
早速確認していきましょう。
■環境
Microsoft Windows XP Pro
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 – Production
With the Partitioning, OLAP and Data Mining options
■はじめ！！！
テストデータは以下の通り。ついでにSCN番号と時間も確認しておきます。
SQL> SELECT A.*,ORA_ROWSCN FROM FLASHBACK_TEST A;
COL1 COL ORA_ROWSCN
---------- --- ----------
1 111 8303822
2 222 8303822
3 333 8303822
4 444 8303822
5 555 8303822
SQL> SELECT SYSTIMESTAMP FROM DUAL;
SYSTIMESTAMP
----------------------------------------------------------------------
05-02-10 17:01:39.131408 +09:00
ではここからCOL1列に対して順次UPDATEとCOMMITを繰り返します。
便宜上SQLに番号を振ります。
SQL> --(1)
SQL> UPDATE FLASHBACK_TEST SET COL2='AAA' WHERE COL1=1;
1行が更新されました。
SQL> commit;
コミットが完了しました。
SQL> --(2)
SQL> UPDATE FLASHBACK_TEST SET COL2='BBB' WHERE COL1=2;
1行が更新されました。
SQL> COMMIT;
コミットが完了しました。
SQL> --(3)
SQL> UPDATE FLASHBACK_TEST SET COL2='CCC' WHERE COL1=3;
1行が更新されました。
SQL> COMMIT;
コミットが完了しました。
SQL> --(4)
SQL> UPDATE FLASHBACK_TEST SET COL2='aaa' WHERE COL1=1;
1行が更新されました。
SQL> COMMIT;
コミットが完了しました。
SQL> --最終形
SQL> SELECT A.*,ORA_ROWSCN FROM FLASHBACK_TEST A;
COL1 COL ORA_ROWSCN
---------- --- ----------
1 aaa 8303924
2 BBB 8303924
3 CCC 8303882
4 444 8303924
5 555 8303924
5分たった事を確認して5分前から現在までの(INTERVAL 5) 行履歴を確認して
みます。
SQL文で使用している擬似列の説明
擬似列 内容 VERSIONS_STRTSCN :当該データへと更新されたSCN VERSIONS_ENDSCN :当該データが更新されたSCN(空白の場合は現在のデータ) VERSIONS_STARTIME :当該データへと、更新された時刻 VERSIONS_ENDTIME :当該データが更新された時刻(空白の場合は現在のデータ) VERSIONS_XID :更新が行われたトランザクションid VERSIONS_OPERATION :更新の内容（I:INSERT U:UPDATE D:DELETE）
SQL> select systimestamp from dual;
SYSTIMESTAMP
----------------------------------------------------------------------
05-02-10 17:06:44.146787 +09:00
SQL> SELECT
VERSIONS_STARTSCN "Start SCN", VERSIONS_ENDSCN "End SCN",
VERSIONS_XID "Xid", VERSIONS_OPERATION "Operation", COL1,COL2
FROM FLASHBACK_TEST
VERSIONS BETWEEN TIMESTAMP
SYSTIMESTAMP - INTERVAL '5' MINUTE
AND
SYSTIMESTAMP;
Start SCN End SCN Xid O COL1 COL
---------- ---------- ---------------- - ---------- ---
8303924 04002B00CD2D0000 U 1 aaa ←(4)
8303882 04001900CD2D0000 U 3 CCC ←(3)
8303864 04001600CD2D0000 U 2 BBB ←(2)
8303839 8303924 04001400CD2D0000 U 1 AAA ←(1)
8303839 1 111
8303864 2 222
8303882 3 333
4 444
5 555
9行が選択されました。
では上記で表示されたSCNを使ってフラッシュクエリーでデータ参照してみます。
SQL> SELECT * FROM FLASHBACK_TEST AS OF SCN 8303924;
COL1 COL
---------- ---
1 aaa
2 BBB
3 CCC
4 444
5 555
SQL> SELECT * FROM FLASHBACK_TEST AS OF SCN 8303882;
COL1 COL
---------- ---
1 AAA
2 BBB
3 CCC
4 444
5 555
SQL> SELECT * FROM FLASHBACK_TEST AS OF SCN 8303864;
COL1 COL
---------- ---
1 AAA
2 BBB
3 333
4 444
5 555
SQL> SELECT * FROM FLASHBACK_TEST AS OF SCN 8303839;
COL1 COL
---------- ---
1 AAA
2 222
3 333
4 444
5 555
SYSDBA接続して「XID」を指定しFLASHBACK_TRANSACTION_QUERYのUNDO_SQL列な
どを参照してみましょう。(4)のUPDATEの時のXIDを指定します。
SQL> CONN / AS SYSDBA
SQL> SELECT TABLE_NAME ,OPERATION,UNDO_SQL
FROM FLASHBACK_TRANSACTION_QUERY
WHERE XID='04002B00CD2D0000'; ←XID指定
TABLE_NAME OPERATION UNDO_SQL
---------- --------------------------- --------------------------------
FLASHBACK_TEST UPDATE update "ORAORA"."FLASHBACK_TEST"
set "COL2" = 'AAA' where ROWID =
'AAANkuAAFAAAAHYAAA';
SQL> select * from "ORAORA"."FLASHBACK_TEST" where ROWID = 'AAANkuAAFAAAAHYAAA';
COL1 COL
---------- ---
1 aaa
しっかりとUNDO_SQLも生成されてますね。
■まとめ
行履歴フラッシュバックでは、データ変更履歴、要は現在のデータに至るまで
にどのような経緯を経てきたのかを確認する事ができます。
キーワードは「VERSIONS BETWEEN」です。
今週はここまで。
何事も、「もれなく、ダブりなく」いきたいものです。 茅ヶ崎にて