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

投稿日: 2005年2月23日

＜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」です。

今週はここまで。

何事も、「もれなく、ダブりなく」いきたいものです。　茅ヶ崎にて