Oracle 11g検証 フラッシュバック・データ・アーカイブ その4

<Oracle 11g検証 フラッシュバック・データ・アーカイブ その4>
ペンネーム: オレンジみかん

■前回のおさらい
前回は履歴レコードの動作についてDELETE分を例にして動作を確認しました。
その結果、DELETEを行うと履歴表へ履歴レコード追加を行うことが確認出来
ました。また、履歴レコードの操作のタイミングはSCNの範囲として記録して
いることが確認出来ました。

■検証
今回も検証で利用しているEMP_MGR表を利用して履歴表の動作を検証したいと
思います。

検証前の履歴レコードの初期状態は以下のようになります。

 SQL> SELECT ROWID,EMPNO,ENAME,DEPTNO,SAL FROM EMP_MGR;

 ROWID                   EMPNO ENAME          DEPTNO        SAL
 ------------------ ---------- ---------- ---------- ----------
 AAARoyAAEAAAAGcAAA       7566 JONES              20       2975
 AAARoyAAEAAAAGcAAB       7698 BLAKE              30       2850
 AAARoyAAEAAAAGcAAF       7782 CLARK              10       2450
 

履歴表には以前の検証で作成されたレコードが存在するため、いったん空の
状態にしたいと思います。
履歴表を空にするには ALTER FLASHBACK ARCHIVE [FDA名] PURGE ALL を実行
します。

 SQL>--SYSユーザー
 SQL> ALTER FLASHBACK ARCHIVE FDA1 PURGE ALL;

 フラッシュバック・アーカイブが変更されました。
 
 SQL> --以降の操作はSCOTTユーザー
 SQL> SELECT STARTSCN,ENDSCN,RID,DEPTNO,OPERATION 
   2 FROM SYS_FBA_HIST_72242;

 レコードが選択されませんでした。

これで履歴表が空の状態になりました。

▼DELETEでの履歴レコードの動作確認
おさらいも踏まえてDELETE時の動作を確認してみましょう。

検証ケースとしてEMP_MGR表のENAMEが’JONES’であるレコードを削除して、
履歴表に履歴レコードを追加してみましょう。

  
 SQL> DELETE FROM EMP_MGR WHERE ENAME='JONES';

 1行が削除されました。

 SQL> COMMIT;

 コミットが完了しました。

 SQL> SELECT ROWID,EMPNO,ENAME,DEPTNO,SAL FROM EMP_MGR;
 
 ROWID                   EMPNO ENAME          DEPTNO        SAL
 ------------------ ---------- ---------- ---------- ----------
 AAARoyAAEAAAAGcAAB       7698 BLAKE              30       2850
 AAARoyAAEAAAAGcAAF       7782 CLARK              10       2450

 SQL> SELECT STARTSCN,ENDSCN,RID,DEPTNO,OPERATION 
   2 FROM SYS_FBA_HIST_72242;
 
   STARTSCN     ENDSCN RID                      DEPTNO OPERATION
 ---------- ---------- -------------------- ---------- ----------
    2424792    2424958 AAARoyAAEAAAAGcAAA           20 I    ←挿入

ROWIDが’AAARoyAAEAAAAGcAAA’のレコードはDELETE文でEMP_MGR表から削除
され、履歴表に履歴レコードが追加されます。

 ※EMP_MGR表に追加される擬似カラム
 RID         :ROWID
 STARTSCN    :履歴レコードが作成されたときの開始システム変更番号(SCN)
 ENDSCN      :履歴レコードが発生する操作(DELETE,UPDATE,INSERT)が終了
              したときの終了システム変更番号(SCN)
 XID         :レコードが作成されたときのトランザクション識別子
 OPERATION   :トランザクションにより実行された操作
              (I:挿入、D:削除、U:更新)

このようにDELETE時の動作では、履歴設定してある表からレコードがDELETE
されると、履歴表の履歴レコードの状態が挿入(OPERATION=I)として履歴
レコードが追加されます。

それではINSERT、UPDATEではどのような動作をするのでしょうか?
それぞれの履歴レコードの作成時の動作について確認してみましょう。

▼INSERTでの履歴レコードの動作確認
EMP_MGR表にENAMEがSCOTTのデータをINSERTして履歴レコードの動作を
確認しましょう。

 SQL> INSERT INTO EMP_MGR (
   2            EMPNO,
   3            ENAME,
   4            JOB,
   5            MGR,
   6            HIREDATE,
   7            SAL,
   8            COMM,
   9            DEPTNO
  10          ) VALUES (
  11            7788,
  12            'SCOTT',
  13            'ANALYST',
  14            7566,
  15            '87-04-19',
  16            '3000',
  17            NULL,
  18            '20'
  19          )
  20  /

 1行が作成されました。

 SQL> COMMIT;

 コミットが完了しました。

 SQL> SELECT ROWID,EMPNO,ENAME,DEPTNO,SAL FROM EMP_MGR;

 ROWID                   EMPNO ENAME          DEPTNO        SAL
 ------------------ ---------- ---------- ---------- ----------
 AAARoyAAEAAAAGcAAB       7698 BLAKE              30       2850
 AAARoyAAEAAAAGcAAC       7788 SCOTT              20       3000←挿入
 AAARoyAAEAAAAGcAAF       7782 CLARK              10       2450
 
 SQL> SELECT ROWID,EMPNO,ENAME,DEPTNO,SAL FROM EMP_MGR;
 
   STARTSCN     ENDSCN RID                      DEPTNO OPERATION
 ---------- ---------- -------------------- ---------- ----------
    2424792    2424958 AAARoyAAEAAAAGcAAA           20 I
 
 

INSERT文では履歴表に履歴レコードは追加されません。

UPDATE文ではどうでしょうか?
▼UPDATEでの履歴レコードの動作確認
続いてUPDATEの履歴表の状態を確認します。ENAME=’JONES’の表を更新して
状態変化を確認してみましょう。

 SQL> UPDATE EMP_MGR MGR SET SAL=3100 WHERE ENAME='BLAKE';

 1行が更新されました。

 SQL> COMMIT;

 コミットが完了しました。

 SQL> SELECT ROWID,EMPNO,ENAME,DEPTNO,SAL FROM EMP_MGR;

 ROWID                   EMPNO ENAME          DEPTNO        SAL
 ------------------ ---------- ---------- ---------- ----------
 AAARoyAAEAAAAGcAAB       7698 BLAKE              30       3100←更新
 AAARoyAAEAAAAGcAAC       7788 SCOTT              20       3000
 AAARoyAAEAAAAGcAAF       7782 CLARK              10       2450

ROWIDが’AAARoyAAEAAAAGcAAB’のレコードに対してSALが3100に更新されました。

 SQL> SELECT STARTSCN,ENDSCN,RID,DEPTNO,OPERATION 
   2 FROM SYS_FBA_HIST_72242;
 
   STARTSCN     ENDSCN RID                      DEPTNO OPERATION
 ---------- ---------- -------------------- ---------- ----------
    2424792    2424958 AAARoyAAEAAAAGcAAA           20 I
    2424792    2425455 AAARoyAAEAAAAGcAAB           30 I        ←挿入
 

UPDATEを発行すると、履歴表にROWIDが’AAARoyAAEAAAAGcAAA’の履歴レコード
が追加されました。

同じROWIDのレコードを更新をした場合、履歴レコードの状態は
更新(OPERATION=U)として履歴レコードが追加されるのでしょうか?
それとも挿入(OPERATION=I)として履歴レコードが追加されるのでしょうか?

それでは、履歴表に同じROWIDの履歴レコードが存在する状態で、
UPDATEを発行してみましょう。

 SQL> UPDATE EMP_MGR MGR SET SAL=3300 WHERE ENAME='BLAKE';

 1行が更新されました。

 SQL> COMMIT;

 コミットが完了しました。

 SQL> SELECT ROWID,EMPNO,ENAME,DEPTNO,SAL FROM EMP_MGR;

 ROWID                   EMPNO ENAME          DEPTNO        SAL
 ------------------ ---------- ---------- ---------- ----------
 AAARoyAAEAAAAGcAAB       7698 BLAKE              30       3300←更新
 AAARoyAAEAAAAGcAAC       7788 SCOTT              20       3000
 AAARoyAAEAAAAGcAAF       7782 CLARK              10       2450

 SQL> SELECT STARTSCN,ENDSCN,RID,DEPTNO,OPERATION 
   2 FROM SYS_FBA_HIST_72242;

   STARTSCN     ENDSCN RID                      DEPTNO OPERATION
 ---------- ---------- -------------------- ---------- ----------
    2424792    2424958 AAARoyAAEAAAAGcAAA           20 I
    2424792    2425455 AAARoyAAEAAAAGcAAB           30 I
    2425455    2425562 AAARoyAAEAAAAGcAAB           30 U       ←更新
 

履歴表に対して既に履歴レコードが存在する場合は、履歴レコードの状態を
更新(OPREATION=U)として履歴レコードが追加されます。

▼まとめ
第3回目、第4回目を通じてFDAの履歴表の動作確認をした結果をまとめると
以下の様になります。

履歴レコードの内容について:
履歴対象テーブルのレコードのトランザクション操作(DELETE、UPDATEなど)
の時に操作対象レコードのROWIDをキーにして履歴表にデータが遷移され、
トランザクション操作のタイミングはSCNを利用してトランザクションの
開始と終了を記録します。

(1)DELETE文発行時

  [FDAで履歴設定された表]              [履歴表]
 +------------------------+           +-----------------------+ 
 | レコードA              |           |                       |
 |                        |           |                       |
 +------------------------+           +-----------------------+
 +------------------------+           +-----------------------+
 | レコードA   (DELETE)   |  DELETE時 |STARTSCN: ENDSCN:ROWID|
 |                        |     →    |レコードA (操作:挿入) |
 +------------------------+           +-----------------------+ 

(2)UPDATE文発行時

  [FDAで履歴設定された表]              [履歴表]
 +------------------------+           +-----------------------+ 
 | レコードB              |           |                       |
 |                        |           |                       |
 +------------------------+           +-----------------------+
 +------------------------+           +-----------------------+
 |                        |  UPDATE時 |STARTSCN: ENDSCN:ROWID|
 | レコードB'  (UPDATE)   |     →    |レコードB (操作:挿入) |
 +------------------------+           +-----------------------+ 
 +------------------------+           +-----------------------+
 |                        |  UPDATE時 |STARTSCN: ENDSCN:ROWID|
 | レコードB'' (UPDATE)   |     →    |レコードB'(操作:更新) |
 +------------------------+           +-----------------------+ 

履歴レコードの作成動作について:
FDAの履歴レコードはUPDATE、DELETEでは履歴表に対して履歴レコードの
追加を行いますが、INSERTでは履歴レコードは作成されません。
これは、FDAが履歴表に更新前情報情報を格納していくことから、INSERTの
ような更新前情報がないケースでは履歴レコードは発生しません。

 
  [FDAで履歴設定された表]              [履歴表]
 +------------------------+ 
 | レコードA              | 
 | レコードB              | 
 +------------------------+ 
 +------------------------+           +-----------------------+
 | レコードA   (DELETE)   |  DELETE時 |                       |
 | レコードB              |     →    |レコードA (操作:挿入) |
 +------------------------+           +-----------------------+
 +------------------------+           +-----------------------+
 | レコードC   (INSERT)   |  INSERT時 |                       |←変化
 | レコードB              |     →    |レコードA (操作:挿入) |  なし
 +------------------------+           +-----------------------+
 +------------------------+           +-----------------------+
 | レコードC   (INSERT)   |  UPDATE時 |レコードA (操作:挿入) |
 | レコードB'  (UPDATE)   |     →    |レコードB (操作:挿入) |
 +------------------------+           +-----------------------+
 +------------------------+           +-----------------------+
 | レコードC   (INSERT)   |  UPDATE時 |レコードA (操作:挿入) |
 | レコードB'' (UPDATE)   |     →    |レコードB (操作:挿入) |
 |                        |           |レコードB'(操作:更新) |
 +------------------------+           +-----------------------+
 ※UPDATEとDELETEでの更新前情報を確保すれば過去のレコードは
   参照可能となる。

今回はここまで

ガソリン代が110円代となり2004年時点の値段に…
それにともない筆者の記憶も2004年をFLASHBACK ARCHIVE! 恵比寿にて