查询oracle 数据库中回滚段中一个时间点被修改的表数据并还原表中原来数据

查询oracle 数据库中回滚段中一个时间点被修改的表数据并还原表中原来数据

利用下面的SQL就可以查处最近更改的数据。

SQL> SELECT ID,NAME,VERSIONS_STARTTIME,VERSIONS_ENDTIME,VERSIONS_OPERATION 
FROM TEST VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE WHERE 
VERSIONS_STARTTIME IS NOT NULL ORDER BY VERSIONS_STARTTIME DESC;

--从test表查找之前被修改的数据
SQL> SELECT * FROM TEST VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE WHERE VERSIONS_STARTTIME IS NOT NULL ORDER BY VERSIONS_STARTTIME DESC;


通过以上小实验可以看出，VERSIONS_STARTTIME是数据被修改的起始时间，VERSIONS_ENDTIME是数据被修改后新数据的有效时间，也就是VERSIONS_STARTTIME和VERSIONS_ENDTIME时间段内，这条数据再没被修改过，如果VERSIONS_ENDTIME为空，就说明这天记录从VERSIONS_STARTTIME时间起再没被修改过。VERSIONS_OPERATION是修改状态，I代表INSERT，U代表UPDATE，D代表DELETE。此时如果想回滚INSERT的数据，只需要DELETE反向操作即可，如果想回滚UPDATE操作，将数据反向UPDATE回去即可，比如本实验已经可以看到进行UPDATE操作的是NAME为史波和孙书祯的两条记录，而且也可以看到进行UPDATE之前的数据他们的性别是男，所以只需要在做个反向UPDATE，将性别该为男即可实现回退，如果要回滚DELETE操作，同样做个INSERT操作，将删除的数据在插回去即可。
注：此SQL只能查询到回滚段内的信息，超出回滚段范围这个SQL就无能为力了，需要借助LOGMGR工具挖掘日志了。（详见：http://www.cnblogs.com/wzmenjoy/archive/2012/01/17/2367636.html）

下面是我写的一个存储，用来还原一个表pub_goods中某个时间点做的数据的更新：

create or replace procedure supplytaxrate_to_his_withdate(hisdate in date) is

cursor c_modifiedgoods is 
SELECT distinct goodsid FROM pub_goods VERSIONS BETWEEN TIMESTAMP MINVALUE AND 
MAXVALUE WHERE VERSIONS_STARTTIME IS NOT NULL and VERSIONS_STARTTIME > hisdate;

r_modifiedgoods c_modifiedgoods%rowtype;

begin


open c_modifiedgoods;
loop

fetch c_modifiedgoods into r_modifiedgoods;
if c_modifiedgoods%notfound then
exit;
end if;

UPDATE pub_goods aa
SET (supplytaxratebak) = (select a.supplytaxrate
from pub_goods VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE a,
(SELECT goodsid,
min(VERSIONS_STARTTIME) VERSIONS_STARTTIME
FROM pub_goods VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
WHERE VERSIONS_STARTTIME IS NOT NULL
and VERSIONS_STARTTIME >
to_date('2017-07-01',
'YYYY-MM-DD')
group by goodsid) b
where a.goodsid = b.goodsid
and a.VERSIONS_STARTTIME = b.VERSIONS_STARTTIME
and a.goodsid=r_modifiedgoods.goodsid)
where aa.goodsid=r_modifiedgoods.goodsid;
end loop;
commit;
close c_modifiedgoods;

end supplytaxrate_to_his_withdate;
/

 

上面的存储中，我是将修改的字段的值还原到一个我新增的bak字段中了，具体如何还原可视情况而定，具体关于insert和delete操作同样可以通过sql查询出数据并做还原。