Oracle10g フラッシュバック アゲインの巻 その３
＜Oracle10g フラッシュバック アゲイン の巻 その３＞
ペンネーム：びー・うぃりー
先週に引き続きフラッシュバックに関する動作確認をしてまいります。
今回は、参照系機能の３つ目「トランザクション履歴フラッシュバック」です。
「トランザクション履歴フラッシュバック」機能は、過去のデータや変更時間
を検索することではなく、データを元に戻す方法を参照する事です。繰り返し
ますが、書き戻しはせず参照するだけです。
トランザクション履歴フラッシュバックでは、FLASHBACK_TRANSACTION_QUERY
ディクショナリ・ビューを参照するため、DBA権限を保持するユーザーで実施
します。
このディクショナリー・ビュですが、実は前回の内容にてさらっと紹介しまし
た。再度確認しましょう。
SQL> DESC FLASHBACK_TRANSACTION_QUERY Name Null? Type ----------------------------------------- -------- ---------------------- XID RAW(8) START_SCN NUMBER START_TIMESTAMP DATE COMMIT_SCN NUMBER COMMIT_TIMESTAMP DATE LOGON_USER VARCHAR2(30) UNDO_CHANGE# NUMBER OPERATION VARCHAR2(32) TABLE_NAME VARCHAR2(256) TABLE_OWNER VARCHAR2(32) ROW_ID VARCHAR2(19) UNDO_SQL VARCHAR2(4000)
概略説明
XID :トランザクションID START_SCN :トランザクションが開始されたSCN START_TIMESTAMP :トランザクションが開始された時間 COMMIT_SCN :トランザクションがコミットされたSCN COMMIT_TIMESTAMP :トランザクションがコミットされた時間 LOGON_USER :トランザクション実行ユーザー UNDO_CHANGE# :UNDO システム変更番号（1 以上） OPERATION :行われた操作 TABLE_NAME :操作対象となった表の名称 TABLE_OWNER :表のオーナー ROW_ID :操作対象行のROWID UNDO_SQL :トランザクションを取り消すSQL文
～～再掲～～
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';
～～～～～～
このように、XIDを指定すれば、そのトランザクションをやり直しするための
SQLが取得できます。したがってそのトランザクション操作すべてをUNDOした
い場合は、それらすべてのUNDO_SQLを実行すればOKでしょう。行履歴フラッシ
ュバックなどでデータ変更経緯を確認しながらUNDO_SQLを実行していく事にな
ります。
早速確認していきましょう。
■環境
Microsoft Windows XP Pro
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 – Production
With the Partitioning, OLAP and Data Mining options
■はじめ！！！
まずテストデータの確認
SQL> select * from flashback_test; C COL2 - ---------- 1 TRN1 2 TRN2 3 TRN3 4 TRN3
順次更新します。
SQL> --Transaction-(4) SQL> update flashback_test set col2='TRN4' where col1=4; 1 row updated. SQL> update flashback_test set col2='TRN4' where col1=1; 1 row updated. SQL> commit; Commit complete. SQL> --Transaction-(5) SQL> update flashback_test set col2='TRN5' where col1=2; 1 row updated. SQL> update flashback_test set col2='TRN5' where col1=3; 1 row updated. SQL> commit; Commit complete. SQL> --Transaction-(6) SQL> update flashback_test set col2='TRN6' where col1=4; 1 row updated. SQL> update flashback_test set col2='TRN6' where col1=1; 1 row updated. SQL> commit; Commit complete. SQL> --最終形 SQL> select * from flashback_test; C COL2 - ---------- 1 TRN6 2 TRN5 3 TRN5 4 TRN6
行履歴を確認しましょう。
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 '15' MINUTE
AND
SYSTIMESTAMP;
Start SCN End SCN Xid O C COL2
---------- ---------- ---------------- - - ----------
527579 01001800B1020000 U 1 TRN6 (6)
527579 01001800B1020000 U 4 TRN6 (6)
527541 03000D00C7060000 U 3 TRN5 (5)
527541 03000D00C7060000 U 2 TRN5 (5)
527076 527579 01001300B1020000 U 1 TRN4 (4)
527076 527579 01001300B1020000 U 4 TRN4 (4)
527076 1 TRN1
527541 2 TRN2
527541 3 TRN3
527076 4 TRN3
10 rows selected.
さぁ、ここから戻します。
上記XIDからUNDO_SQL参照し順番に実行する。ここで気をつけることは紐を解
くようにUNDO_SQLを適用していく事。
SQL> conn / as sysdba
Connected.
SQL> SELECT UNDO_SQL
2 FROM FLASHBACK_TRANSACTION_QUERY
3 WHERE XID='01001800B1020000'; (6)のXIDを指定
UNDO_SQL
---------------------------------------------------------------------
update "ORAORA"."FLASHBACK_TEST" set "COL2" = 'TRN4 ' where ROWID = 'AAAMVJAAFAAAAHVAAA';
update "ORAORA"."FLASHBACK_TEST" set "COL2" = 'TRN4 ' where ROWID = 'AAAMVJAAFAAAAHVAAD';
SQL> SELECT UNDO_SQL
FROM FLASHBACK_TRANSACTION_QUERY
WHERE XID='03000D00C7060000' ←(5)のXIDを指定
UNDO_SQL
---------------------------------------------------------------------
update "ORAORA"."FLASHBACK_TEST" set "COL2" = 'TRN3 ' where ROWID = 'AAAMVJAAFAAAAHVAAC';
update "ORAORA"."FLASHBACK_TEST" set "COL2" = 'TRN2 ' where ROWID = 'AAAMVJAAFAAAAHVAAB';
SQL> SELECT UNDO_SQL
FROM FLASHBACK_TRANSACTION_QUERY
WHERE XID='01001300B1020000'; ←(4)のXIDを指定
UNDO_SQL
---------------------------------------------------------------------
update "ORAORA"."FLASHBACK_TEST" set "COL2" = 'TRN1 ' where ROWID = 'AAAMVJAAFAAAAHVAAA';
update "ORAORA"."FLASHBACK_TEST" set "COL2" = 'TRN3 ' where ROWID = 'AAAMVJAAFAAAAHVAAD';
上記UNDO_SQLを実行します。（SQLは単純なため省略します。）
・・・
SQL> select * from flashback_test; C COL2 - ---------- 1 TRN1 2 TRN2 3 TRN3 4 TRN3
と戻りました。今週はここまで。
その昔剣道道場に通ってました。道場・・その言葉が懐かしいです。茅ヶ崎にて