闪回delete 恢复

柔情只为你懂 2022-08-09 09:26 216阅读 0赞

误操作语句：

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页