Oracle Flashback QueryをSQL Developerでつかう

Oracle Flashback Technologyという機能は、たとえばついうっかりDELETEしちゃった行を元に戻すのに使える。EE以上で使用可能。SQL Developerに依存する機能ではないが、SCNの確認程度には便利なので、せっかくなので使う。

環境

• DB
  • CentOS-6.5-x86_64
  • Oracle Database 11g Release 2(11.2.0.1.0) Enterprise Edition
• ツール
  • Oracle SQL Developer 4.0.3(4.0.3.16.84)
  • rlogin Version 2.16.6

やること

テストテーブルに適当な適当なデータをINSERTしてから全件DELETEする。そのあと、全件DELETE前のデータで復元可能なことを確認する。

準備

後述するOracle Flashback Transaction Queryを使うため、サプリメンタル・ロギングを有効にする。

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

テストテーブルにデータ入れる。なお、説明を分かりやすくするため、各COMMIT間は1分以上間隔を開けている。

DROP TABLE hoge2 PURGE;
CREATE TABLE hoge2( col1 VARCHAR2(30));

INSERT INTO hoge2(col1) VALUES('TEST1');
INSERT INTO hoge2(col1) VALUES('TEST2');
INSERT INTO hoge2(col1) VALUES('TEST3');
COMMIT;

INSERT INTO hoge2(col1) VALUES('TEST4');
COMMIT;

--←この時点の状態に戻したい。

UPDATE hoge2 SET col1 = 'TEST3->555' WHERE col1 = 'TEST3';
COMMIT;

DELETE FROM hoge2;
COMMIT;

Oracle Flashback Query

前提として、UPDATEより前の状態に戻したい、とする。

SQL Developerで、表 -> HOGE2 -> フラッシュバックを確認する。

UpdateのVERSIONS_STARTSCNが1239234なのが確認できる。ということは、それより1つ以上少ないSCNのどこか、つまり1239234なら恐らく望む結果が得られるだろう、と推測できる。

というわけで、SCN=1239233のhoge2テーブルの状態を参照する。

SELECT * FROM hoge2
AS OF SCN (1239233);

以下のような結果が返ってくる。

COL1
TEST1
TEST2
TEST3
TEST4

あとは上記の結果をINSERT .. SELECT ..するなりすれば良い。

また、SCNではなく、ある時点のTIMESTAMPに基づいてテーブルの状態を参照することも出来る。

SELECT * FROM hoge2
AS OF TIMESTAMP
TO_TIMESTAMP('2014-12-21 16:25:56', 'YYYY-MM-DD HH24:MI:SS');

Oracle Flashback Version Query

上記ではSCNもしくはTIMESTAMPをSQL Developerの機能を使用して確認したが、SQLでこれを確認するにはOracle Flashback Version Queryを実行すればよい。SQL Developerの該当機能はこのSQLのラッパーに過ぎない。

SELECT
  versions_startscn,
  versions_starttime,
  versions_endscn,
  versions_endtime,
  versions_xid,
  versions_operation
FROM hoge2
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;

実行結果は以下の通り。先のSQL Developerのキャプチャとおおむね同じ結果が出力されているのが分かる。

VERSIONS_STARTSCN	VERSIONS_STARTTIME	VERSIONS_ENDSCN	VERSIONS_ENDTIME	VERSIONS_XID	VERSIONS_OPERATION
1239308	2014-12-21 16:28:56.000000000			01000B00D5030000	"D"
1239308	2014-12-21 16:28:56.000000000			01000B00D5030000	"D"
1239308	2014-12-21 16:28:56.000000000			01000B00D5030000	"D"
1239308	2014-12-21 16:28:56.000000000			01000B00D5030000	"D"
1239234	2014-12-21 16:26:56.000000000	1239308	2014-12-21 16:28:56.000000000	05001B00D7040000	"U"
1239112	2014-12-21 16:25:14.000000000	1239308	2014-12-21 16:28:56.000000000	02001000A8040000	"I"
1239083	2014-12-21 16:24:17.000000000	1239234	2014-12-21 16:26:56.000000000	03001100E8040000	"I"
1239083	2014-12-21 16:24:17.000000000	1239308	2014-12-21 16:28:56.000000000	03001100E8040000	"I"
1239083	2014-12-21 16:24:17.000000000	1239308	2014-12-21 16:28:56.000000000	03001100E8040000	"I"

TIMESTAMPベースで出すことも可能。

SELECT
  versions_startscn,
  versions_starttime,
  versions_endscn,
  versions_endtime,
  versions_xid,
  versions_operation
FROM hoge2
VERSIONS BETWEEN TIMESTAMP
TO_TIMESTAMP('2014-12-21 16:24:17', 'YYYY-MM-DD HH24:MI:SS')
AND TO_TIMESTAMP('2014-12-21 16:31:56', 'YYYY-MM-DD HH24:MI:SS');

Oracle Flashback Transaction Query

トランザクションのメタデータなどが確認できる。

ためしに、DELETEのXIDを指定してみる。

SELECT xid, operation, start_scn, commit_scn, logon_user, undo_sql
FROM flashback_transaction_query
WHERE xid = HEXTORAW('01000B00D5030000');

結果はこんな感じ。

XID	OPERATION	START_SCN	COMMIT_SCN	LOGON_USER	UNDO_SQL
01000B00D5030000	DELETE	1239306	1239308	KAGAMIHOGE	insert into "KAGAMIHOGE"."HOGE2"("COL1") values ('TEST4');
01000B00D5030000	DELETE	1239306	1239308	KAGAMIHOGE	insert into "KAGAMIHOGE"."HOGE2"("COL1") values ('TEST3->555');
01000B00D5030000	DELETE	1239306	1239308	KAGAMIHOGE	insert into "KAGAMIHOGE"."HOGE2"("COL1") values ('TEST2');
01000B00D5030000	DELETE	1239306	1239308	KAGAMIHOGE	insert into "KAGAMIHOGE"."HOGE2"("COL1") values ('TEST1');
01000B00D5030000	BEGIN	1239306	1239308	KAGAMIHOGE

面白いのはUNDO_SQLで、マニュアルによると、論理的にそのトランザクションを取り消すクエリ、とある。確かに上記の結果を見る限り、DELETEを取り消すINSERTになっている。ただし、マニュアルに態々記述がある「論理的に」とはどういう意味だろうか。

まず、DELETEの前に実行したUPDATEのundo_sqlを確認してみる。

SELECT xid, operation, start_scn, commit_scn, logon_user, undo_sql
FROM flashback_transaction_query
WHERE xid = HEXTORAW('05001B00D7040000');

これの結果はこんな感じ。

XID	OPERATION	START_SCN	COMMIT_SCN	LOGON_USER	UNDO_SQL
05001B00D7040000	UPDATE	1239233	1239234	KAGAMIHOGE	update "KAGAMIHOGE"."HOGE2" set "COL1" = 'TEST3' where ROWID = 'AAARWYAAEAAABHPAAC';
05001B00D7040000	BEGIN	1239233	1239234	KAGAMIHOGE

update文はROWIDベースになっている。一意インデックスが無いからこういう結果になるのかもしれないが、ともかくこの環境ではその行は既にDELETEされてしまっているので、このUPDATEを実行しても何も起こらない。「論理的に」とは、物理的なROWIDのレベルでも同じ結果になるとは限らない、くらいの意味なことが確認できる。

なお、SQL Developerのフラッシュバックビューの場合、下側のUNDO SQLタブで確認できる。

参考：SQL Developerのフラッシュバックビューで実行されるSQL

当該機能を開いて右クリック -> グリッドをレポートとして保存、を選択すると実行されるSQLが表示される。

SELECT "Operation", "VERSIONS_XID", "VERSIONS_STARTSCN", "VERSIONS_ENDSCN", "ROW_ID", "NOW_SCN" FROM(
SELECT  DECODE( versions_operation
         , 'I', 'Insert'
         , 'U', 'Update'
         , 'D', 'Delete'
              , 'Original'
             ) "Operation"
,             versions_xid
,      versions_startscn
,      versions_endscn     
,rowid row_id       
,TIMESTAMP_TO_SCN(systimestamp) now_scn
, 'KAGAMIHOGE' OBJECT_OWNER
, 'HOGE2' OBJECT_NAME
FROM   "KAGAMIHOGE"."HOGE2" /*ignore ORA-30051*/
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
)

参考文献

• Oracle Databaseアドバンスト・アプリケーション開発者ガイド11gリリース2 (11.2) 12 Oracle Flashback Technologyの使用
• oracle - the value of undo_sql in flashback_transaction_query view is always null - Database Administrators Stack Exchange - UNDO_SQL列がnullになるのはなぜ？　に対する回答。サプリメンタル・ロギングがOFFになっているのでALTER DATABASE ADD SUPPLEMENTAL LOG DATA;を実行してください、とある。