Oracle 11g検証 フラッシュバック・データ・アーカイブ その４
＜Oracle 11g検証 フラッシュバック・データ・アーカイブ その４＞
ペンネーム： オレンジみかん
■前回のおさらい
前回は履歴レコードの動作について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！ 恵比寿にて