Oracle10g フラッシュバック アゲインの巻 その3

<Oracle10g フラッシュバック アゲイン の巻 その3>
ペンネーム:びー・うぃりー

先週に引き続きフラッシュバックに関する動作確認をしてまいります。
今回は、参照系機能の3つ目「トランザクション履歴フラッシュバック」です。

「トランザクション履歴フラッシュバック」機能は、過去のデータや変更時間
を検索することではなく、データを元に戻す方法を参照する事です。繰り返し
ますが、書き戻しはせず参照するだけです。

トランザクション履歴フラッシュバックでは、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

と戻りました。今週はここまで。

その昔剣道道場に通ってました。道場・・その言葉が懐かしいです。茅ヶ崎にて