豆知識17 フラッシュバッククエリを使ったUpdate文で、データ復旧

フラッシュバック問い合わせとUpdate文を組み合わせた、テーブルデータのリカバリ方法


create table TestTable(PKey primary key,ColA,ColB) as
select 1,'F','F' from dual union
select 2,'F','T' from dual union
select 3,'T','F' from dual union
select 4,'T','T' from dual;

--間違ってテーブルデータを更新してしまった・・・
update TestTable set
ColA = 'X',
ColB = 'X';

col ColA for a10
col ColB for a10
select * from TestTable;

--キーでJoinしてupdateして、データをリカバリ(UpdatableViewを使う方法)
update (
select
a.ColA as NewColA , b.ColA as oldColA,
a.ColB as NewColB , b.ColB as oldColB
from TestTable as of TimeStamp TimeStamp '2014-05-05 10:13:00' a
Join TestTable b using(PKey))
set oldColA = NewColA,
    oldColB = NewColB;

--キーでJoinしてupdateして、データをリカバリ(スカラーサブクエリを使う方法)
update TestTable a
set (ColA,ColB) =
(select b.ColA,b.ColB from TestTable as of TimeStamp TimeStamp '2014-05-05 10:13:00' b
  where b.PKey = a.PKey);

--DeleteしてInsertでも可
delete from TestTable;
insert into TestTable
select * from TestTable as of TimeStamp TimeStamp '2014-05-05 10:13:00';