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

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

今週も引き続きフラッシュバック・データ・アーカイブ(以下、FDA)の検証
を行います。

■前回のおさらい
・FDAの実行環境を構築
FDAを利用するには、以下の(1)~(4)の流れでFDAの利用環境を構築します。
(1)フラッシュバック・データ・アーカイブ領域の作成
※SYSユーザーで操作

 SQL> --表領域の作成
 SQL> CREATE TABLESPACE FDA1_TS1 DATAFILE '/u01/app/oracle/oradata
 /insight/fda1_ts1.dbf' SIZE 1G;

(2)FDAの作成

 SQL> CREATE FLASHBACK ARCHIVE FDA1 TABLESPACE FDA1_TS1 QUOTA 500M
  2   RETENTION 1YEAR;

(3)FDAの実行権限の設定

 SQL> --権限の付与
 SQL> GRANT FLASHBACK ARCHIVE ON FDA1 TO SCOTT;

(4)テーブルをFDAの履歴管理対象として設定
履歴データを取得できるようにFDAを設定します。
※前回検証用に作成したEMP_MGR表に対して履歴データを設定する場合

 SQL> ALTER TABLE EMP_MGR FLASHBACK ARCHIVE FDA1;
 

・FDAでの過去レコードの参照
FDAは今までのフラッシュバック機能(フラッシュバッククエリ)と同様に
SELECT文にAS OF [時間]を付けて実行することで過去レコードを参照します。

SELECT [カラム名] FROM [テーブル名] AS OF [時間]

・今までのフラッシュバック機能とFDAとの過去レコード参照の違い
履歴情報はFDAではUNDOデータとは別の場所に保存されるため、今までの
フラッシュバック機能のようにUNDO管理を意識する必要が無くなりました。
そのため、FDAはUNDOデータの制約を意識すること無く保存期間を長く設定
出来るようになりました。

実際にFDAの履歴表の中身を確認してみましょう。

▼レコードの履歴管理
FDAに格納される履歴表名を確認するには、FDAの表に関する情報を管理する
テーブル(USER_FLASHBACK_ARCHIVE_TABLES)を参照することで確認することが
出来ます。

□USER_FLASHBACK_ARCHIVE_TABLESの内容
USER_FLASHBACK_ARCHIVE_TABLESは、現行ユーザが所有する、FDAが有効な
表に関する情報が表示されます。

    TABLE_NAME:
      フラッシュバック・アーカイブが有効な表の名前 
    OWNER_NAME:
      フラッシュバック・アーカイブが有効な表の所有者の名前
    FLASHBACK_ARCHIVE_NAME:
      フラッシュバック・アーカイブの名前 
    ARCHIVE_TABLE_NAME:
      ユーザー表の履歴データを含むアーカイブ表の名前 

それでは、EMP_MGR表はどのような履歴表名で管理されているのでしょうか?
USER_FLASHBACK_ARCHIVE_TABLESのFLASHBACK_ARCHIVE_NAME列に注目して
EMP_MGR表の履歴表名を確認してみましょう。

 SQL> SELECT * FROM  USER_FLASHBACK_ARCHIVE_TABLES;

 TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME
 ---------- ---------- ---------------------- --------------------
 EMP_MGR    SCOTT      FLA1                   SYS_FBA_HIST_72242

USER_FLASHBACK_ARCHIVE_TABLESの内容から、EMP_MGR表に対する履歴表が
SYS_FBA_HIST_72242という名前で作成されていることがわかります。

EMP_MGRの履歴表であるSYS_FBA_HIST_72242表の中身を確認してみましょう。

□履歴表の内容確認
SYS_FBA_HIST_72242という履歴表の定義を確認するとEMP_MGR表に5つの
擬似カラムが追加された表になっています。

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

履歴表の擬似カラムの中で、注目はRID、STARTSCN、ENDSCNです。
擬似カラムを見たところ、FDAはSCNでレコードの有効範囲を決めて
動作しているのでしょうか?

それでは実際にDELETE文を発行し、履歴レコードの状態の移り変わりを確認
して見ましょう。
(1)DELETE前の確認
履歴管理されたEMP_MGR表をSELECTした時のSCNとの関係

 SQL> --DELETE前のEMP_MGR表の状態
 SQL> SELECT ROWID,EMPNO,ENAME,DEPTNO,SAL FROM EMP_MGR;

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

 SQL> --履歴テーブルの状態
 SQL> SELECT STARTSCN,ENDSCN,RID,DEPTNO,OPERATION 
   2   FROM SYS_FBA_HIST_72242;

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

現在のSCNは?

 SQL>--[SYSユーザー]
 SQL> SELECT CURRENT_SCN,TO_CHAR(systimestamp,'yyyy/mm/dd hh24: 
   2   mi:ss') datetime FROM V$DATABASE;

 CURRENT_SCN DATETIME
 ----------- --------------------
     2223403 2008/11/22 17:00:15
     

SELECT時の履歴レコードのSTARTSCN、ENDSCN、CURRENT_SCNの関係は

 STARTSCN   ENDSCN   DEPTNO           CURRENT_SCN
 -------- --------  -------          ------------
                                          2223403

(2)DELETE後のEMP_MGR表の状態

 SQL> --EMP_MGR表の履歴レコードの状態
 SQL> DELETE FROM EMP_MGR WHERE DEPTNO=20;

 1行が削除されました。

 SQL> COMMIT;

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

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

 ROWID                   EMPNO ENAME          DEPTNO        SAL
 ------------------ ---------- ---------- ---------- ----------
 AAARoyAAEAAAAGcAAE       7698 BLAKE              30       2850
 AAARoyAAEAAAAGcAAF       7782 CLARK              10       2450
 

履歴レコードの状態を確認すると・・・

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

   STARTSCN     ENDSCN RID                      DEPTNO OPERATION
 ---------- ---------- -------------------- ---------- ----------
    2223305    2223515 AAARoyAAEAAAAGcAAD           20 I
                       ↑↑
             DELETE操作した時の履歴レコード

削除されたROWID=AAARoyAAEAAAAGcAADのレコードがSTARTSCN=2223305、
ENDSCN=2223515として履歴表に追加されました。

現在のSCNは?

 SQL>--[SYSユーザー]
 SQL>  SELECT CURRENT_SCN,TO_CHAR(systimestamp,'yyyy/mm/dd hh24: 
   2    mi:ss') datetime FROM V$DATABASE;

 CURRENT_SCN DATETIME
 ----------- --------------------
     2223742 2008/11/22 17:10:49

DELETEした履歴レコードのSTARTSCN、ENDSCN、CURRENT_SCNの関係は

 STARTSCN   ENDSCN      CURRENT_SCN
 -------- --------     ------------
  2223305   2223515  <      2223742

以上のように、FDAではDELETE操作を行うと履歴表に行を追加し、操作の
タイミングをSCNの範囲として記録しているようです。

今回はここまで

ドル安くなりましたね。でも海外に遊びに行く予定がなーい! 恵比寿にて