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

投稿日: 2008年12月03日

<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! 恵比寿にて