用法示例:

(1) 找出删除前的数据:

SELECT ename, sal FROM emp AS OF TIMESTAMP (SYSTIMESTAMP – INTERVAL ‘6’ MINUTE)
WHERE ename = ‘ALLEN’;

SELECT ename, sal FROM emp AS OF SCN 3297508 where ename=’MILLER’

(2) 把表中的数据加回来:

insert into t1 (select * from t1 as of timestamp(systimestamp – interval ‘15’ minute)) ;

(3) 如果表中一个数据被错误修改:

update emp
set sal = (
SELECT sal FROM emp
AS OF TIMESTAMP (SYSTIMESTAMP – INTERVAL ‘15’ MINUTE) where ename=’ALLEN’)
where ename=’ALLEN’;

(4) recovery accidental delete from table 恢复全表:

DECLARE

CURSOR FLASH_RECOVER IS
select * from emp_recover; emp_recover_rec emp_recover%ROWTYPE;
begin execute dbms_flashback.enable_at_time(to_date(‘2005-12-07 9:00:00’,’YYYY-MM-DD HH24:MI:SS’)) ; open FLASH_RECOVER; DBMS_FLASHBACK.DISABLE; loop FETCH FLASH_RECOVER INTO emp_recover_rec; EXIT WHEN FLASH_RECOVER%NOTFOUND; insert into emp_recover values (emp_recover_rec.empno, emp_recover_rec.ename, emp_recover_rec.job, emp_recover_rec.mgr, emp_recover_rec.hiredate, emp_recover_rec.sal, emp_recover_rec.comm, emp_recover_rec.deptno); end loop; CLOSE FLASH_RECOVER; commit;
Exception when others then DBMS_FLASHBACK.DISABLE; CLOSE FLASH_RECOVER; rollback;
end;

局限性:
(1) Oracle每5分钟做一个恢复点, 虽然你指定要恢复到8:00时的数据, Oracle可能回恢复到7:56或者8:04时的数据.

(2) Oracle不能在一个语句里参照一个表在不同时间段的版本. 上面的(4)例子就是为了克服这个问题. 

6 Comments to “用FlashBack query恢复误删数据”

1. online directory main — May 18, 2006 @ 10:30 am
   

   hello! http://www.dirare.com/Sweden/ online directory. SMART Yellow Pages, About DIRare, Search in Business Category. From online directory .

2. fxndmwz vbkp — September 3, 2006 @ 12:35 am
   

   khiuemfvr fqsvulych yesguhliz bcln gujwpr kfdli kvntpr

3. lnzxot koslmiw — September 3, 2006 @ 12:35 am
   

   nfluwkpzm fjhbgp cxkf ecjny vdqutzn lnhockyi smuq bdhi rxpbqyimh

4. acorn slipper sock — September 3, 2006 @ 2:00 pm
   

   Very good site. Thanks!
   acorn slipper sock

5. acorn-slipper-sock.rusdoor.com acorn slipper sock — September 5, 2006 @ 9:57 am
   

   Nice site. Thank you:-)
   acorn-slipper-sock.rusdoor.com acorn slipper sock

6. yelang9809 — May 23, 2007 @ 3:15 pm
   

   好东东！
   以后多来学习学习！

Write a comment

You need tologin.