闪回delete 恢复-蒲公英云

闪回delete 恢复

误操作语句：

DELETE FROM dbwlhr.t_hrcustom_salarycheck WHERE fid in(
SELECT tt.fid FROM(
SELECT
t.fid,
t.psncode,
t.f_item,
t.f_itemdate,
t.content,
RANK() OVER(PARTITION BY t.psncode ORDER BY t.fid ) r
FROM dbwlhr.t_hrcustom_salarycheck t WHERE t.cyear=’2015’ AND t.cperiod=’09’
AND t.verifyopinion IS NULL
)tt
WHERE tt.r>1);
DELETE FROM dbwlhr.t_hrcustom_salarycheck WHERE fid IN
(SELECT tt1.fid FROM (
SELECT
t.fid,
t.psncode,
t.f_item,
t.f_itemdate,
t.content,
RANK() OVER(PARTITION BY t.psncode ORDER BY t.fid ) r
FROM dbwlhr.t_hrcustom_salarycheck t WHERE t.cyear=’2015’ AND t.cperiod=’09’)tt1
WHERE tt1.r>1)
AND verifyopinion IS NULL;

commit

8079row,39row

恢复方法：

insert into dbwlhr.t_hrcustom_salarycheck

select * FROM dbwlhr.t_hrcustom_salarycheck as of timestamp to_timestamp(‘2015-10-17 11:21:25’, ‘YYYY-MM-DD HH24SS’)
WHERE fid in (

SELECT tt.fid FROM(
SELECT
fid,
psncode,
f_item,
f_itemdate,
content,
RANK() OVER(PARTITION BY psncode ORDER BY fid ) r
FROM dbwlhr.t_hrcustom_salarycheck as of timestamp to_timestamp(‘2015-10-17 11:21:25’, ‘YYYY-MM-DD HH24SS’)
WHERE cyear=’2015’ AND cperiod=’09’
AND verifyopinion IS NULL
)tt
WHERE tt.r>1);

cimmit

8079row

insert into dbwlhr.t_hrcustom_salarycheck
select *
FROM dbwlhr.t_hrcustom_salarycheck as of timestamp to_timestamp(‘2015-10-17 11:21:31’, ‘YYYY-MM-DD HH24SS’)
WHERE fid in (SELECT t.fid
FROM (SELECT fid,
psncode,
f_item,
f_itemdate,
content,
RANK() OVER(PARTITION BY psncode ORDER BY fid) r
FROM dbwlhr.t_hrcustom_salarycheck as of timestamp to_timestamp(‘2015-10-17 11:21:31’, ‘YYYY-MM-DD HH24SS’)
WHERE cyear = ‘2015’
AND cperiod = ‘09’) t
where t.r > 1)
and verifyopinion IS NULL

and fid not in

(SELECT tt.fid FROM(
SELECT
fid,
psncode,
f_item,
f_itemdate,
content,
RANK() OVER(PARTITION BY psncode ORDER BY fid ) r
FROM dbwlhr.t_hrcustom_salarycheck as of timestamp to_timestamp(‘2015-10-17 11:21:25’, ‘YYYY-MM-DD HH24SS’)
WHERE cyear=’2015’ AND cperiod=’09’
AND verifyopinion IS NULL
)tt
WHERE tt.r>1);

commit

39 row

原理：闪回查询数据重新插入

其它方法：dbms_flashback.transaction_backout参考临危不惧11g恢复 260页