読者です 読者をやめる 読者になる 読者になる

kagamihogeの日記

kagamihogeの日記です。

Oracle Flashback QueryをSQL Developerでつかう

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

環境

やること

テストテーブルに適当な適当なデータを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 -> フラッシュバックを確認する。

f:id:kagamihoge:20141221165045j:plain

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タブで確認できる。

f:id:kagamihoge:20141221171639j:plain

参考: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
)

参考文献