フラッシュバッククエリーに関する検証　その４

～フラッシュバッククエリーに関する検証　その４～
ペンネーム　ちゃむ

前回は、フラッシュバッククエリーで実際に1時間前のデータを取得できる様子
を示した。但し、データをテーブルなどに格納しようとすると、以下のように、
SQL*PLUSより実行してORA-08182が発生してしまった。

SQL> alter session set NLS_TIMESTAMP_FORMAT = 'yyyy/mm/dd hh24:mi:ss';
Session altered.
SQL>  execute dbms_flashback.enable_at_time('2001/12/26 12:54:44');
PL/SQL procedure successfully completed.
SQL> insert into b select * from a;
ORA-08182: operation not supported while in Flashback mode

今回は、このフラッシュバッククエリーで検索したデータをテーブル
などに格納する方法を説明する。また、オブジェクトの定義が変わってしまっ
たときには、どうなるのかも検証してみる。

それを実現するために使用するのは、超定番のPL/SQLである。
ポイントは、DBMS_FLASHBACK.enable_at_timeで時間してした後、カーソルに
そのデータを事前に格納しておくことと、更新処理（今回はINSERT文）を実行する前に
DBMS_FLASHBACK.DISABLE;でフラッシュバッククエリーを無効にすることである。

＜PL/SQLで実現！！＞

１．既にテーブルAという14行のデータが存在することを確認し、事前に空のテ
ーブルBを作成しておく。

SQL> SELECT COUNT(*) FROM A;
   COUNT(*)
----------
        14

SQL> CREATE TABLE B AS SELECT * FROM A WHERE 1=2;
Table created.

SQL> SELECT COUNT(*) FROM B;
  COUNT(*)
----------
         0

２．以下のPL/SQLブロックをそのまま、SQL*PLUSにコピー＆ペーストで実行してみる。

------------------------ PL/SQLブロック ------------------------
DECLARE
    cursor A_CUR  is  SELECT  * FROM A;
    A_DATA  A_CUR%rowtype;
BEGIN
DBMS_FLASHBACK.enable_at_time(to_timestamp('2001/12/26 12:54:44', 'YYYY-MM-DD HH24:MI:SS'));
/* DBMS_FLASHBACK.DISABLEをする前にカーソルをオープンしておくこと*/
OPEN A_CUR;
/*INSERT 処理を行なう前に、フラッシュバッククエリーを無効にする*/
DBMS_FLASHBACK.DISABLE;

LOOP
   FETCH A_CUR INTO A_DATA;
   EXIT WHEN A_CUR%NOTFOUND;
   INSERT INTO B VALUES(A_DATA.EMPNO,A_DATA.ENAME,A_DATA.JOB,A_DATA.MGR,
                       A_DATA.HIREDATE,A_DATA.SAL,A_DATA.COMM,A_DATA.DEPTNO);
END LOOP;

COMMIT;
END;
/
------------------------ PL/SQLブロック ------------------------

PL/SQL procedure successfully completed.

３．テーブルBのデータの中身を確認

SQL> SELECT COUNT(*) FROM  B;

  COUNT(*)
----------
        14

フラッシュバッククエリーの結果を別テーブルに格納できた！！
この機能を使えば、同じテーブルの現在のデータと過去のデータの
比較などもできることが想像つくであろう。

＜オブジェクトの定義が変わってしまったとき＞

さて、話は変わるが、テーブルが存在しないときや定義を変更してしまった場合は、
どうなるのであろうか？結果から言うとエラーが発生する。つまり、DROP TABLEして
しまった場合、フラッシュバッククエリーでは、残念ながらデータを取得できない。
ログマイナーであればORACLE9iのサプリメンタル・ロギングの機能を使用していれば、
DROP TABLEしたテーブルでも復旧できる。詳しくは、ログマイナに関する検証
その六をご覧いただきたい。

＜テーブルが既にドロップされてしまった場合＞

SQL> drop table a;
SQL> create table a as select * from emp where 1=2;

SQL>  alter session set NLS_TIMESTAMP_FORMAT = 'yyyy/mm/dd hh24:mi:ss';
Session altered.
SQL> execute dbms_flashback.enable_at_time('2001/12/26 12:54:44');
PL/SQL procedure successfully completed.
SQL> select count(*) from a;
ORA-00942: table or view does not exist

＜テーブルの定義が変更された場合＞

SQL>  alter session set NLS_TIMESTAMP_FORMAT = 'yyyy/mm/dd hh24:mi:ss';
Session altered.
SQL> execute dbms_flashback.enable_at_time('2001/12/26 12:54:44');
PL/SQL procedure successfully completed.
SQL> select count(*) from a;
ORA-01466: unable to read data - table definition has changed

以上苦しいときも、悲しいときも茅ケ崎にて

フラッシュバッククエリーに関する検証 その４

フラッシュバッククエリーに関する検証　その４