闪回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 HH24:MI:SS')
 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 HH24:MI:SS')
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 HH24:MI:SS')
 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 HH24:MI:SS')
                          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 HH24:MI:SS')
WHERE cyear='2015' AND cperiod='09'
AND verifyopinion IS NULL
)tt
WHERE tt.r>1);

commit

39 row

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

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