使用Oracle10g的Flashback Query进行数据闪回

Oracle10g对于闪回查询进行了增强，支持更简单的SQL操作，允许对误删除、误更新等DML操作进行闪回。 看一下以下测试: 1.原表记录 $ sqlplus eygle/eygle SQL*Plus: Release 10.1.0.2.0 - Production on Wed Mar 30 08:52:04 2005 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production With the Partitioning, OLAP and Data Mining options EYGLE on 30-MAR-05 >select count(*) from t1; COUNT(*) ---------- 9318 2.误删除所有记录 并且提交更改。 EYGLE on 30-MAR-05 >delete from t1; 9318 rows deleted. EYGLE on 30-MAR-05 >commit; Commit complete. EYGLE on 30-MAR-05 >select count(*) from t1; COUNT(*) ---------- 0 3.获得当前SCN 如果能够确切知道删除之前SCN最好，如果不知道，可以进行闪回查询尝试. EYGLE on 30-MAR-05 >select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------ 10671006 EYGLE on 30-MAR-05 >select count(*) from t1 as of scn 10671000; COUNT(*) ---------- 0 EYGLE on 30-MAR-05 >select count(*) from t1 as of scn 10670000; COUNT(*) ---------- 9318 我们看到在SCN=10670000时，数据都在。 4.恢复数据. EYGLE on 30-MAR-05 >insert into t1 select * from t1 as of scn 10670000; 9318 rows created. EYGLE on 30-MAR-05 >commit; Commit complete. EYGLE on 30-MAR-05 >select count(*) from t1; COUNT(*) ---------- 9318 EYGLE on 30-MAR-05 >