Oracle9i 中新增的闪回查询（Flashback Query）功能对于误删除或者误更新并且已经commit了的情况，提供了简便快捷的恢复方法，所谓的Flashback Query就是利用Oracle 多版本读一致的特性，在需要的时候通过undo 来提供所需的前镜像中的数数据。利用这个功能，可以看到历史数据，甚至用历史数数据来修复误操作引起的错误。可以通过指定时间或者SCN 来检索需要的数据。前提条件是数据库必须处于Automatic Undo Management 状态。最大可以闪回查询的时间段由UNDO_RETENTION 初始化参数（单位为秒）指定。可以通过ALTER SYSTEM SET UNDO_RETENTION = ;来修改参数值。如何使用Flashback Query呢？使用SELECT 语句的AS OF 来进行闪回查询。使用AS OF 关键字来对表，视图，或者物化视图进行Flashback Query，如果指定了SCN，那么expr 部分必须是一个数字，如果指定了TIMESTAMP，那么expr 必须是一个timestamp类型的值。查询结果将返回在指定的SCN 或者时间点上的数据。

下面提供一个恢复的例子：

ctoc@ORCL> show parameter undo_retention

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_retention integer 10800

ctoc@ORCL> show parameter undo_management

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string AUTO

ctoc@ORCL> select count(*) from esms;

COUNT(*)

----------

207

ctoc@ORCL> delete esms;

207 rows deleted.

ctoc@ORCL> commit

2 ;

Commit complete.

ctoc@ORCL> select count(*) from esms;

COUNT(*)

----------

0

ctoc@ORCL> col fscn for 99999999999

ctoc@ORCL> col nscn for 99999999999

ctoc@ORCL> select name,FIRST_CHANGE# fscn,NEXT_CHANGE# nscn,FIRST_TIME from v$archived_log;

NAME

----------------------------------------------------------------------------------------------------

FSCN NSCN FIRST_TIM

------------ ------------ ---------

/oracle/oradata/orcl/archivelog/1_99.dbf

10741133432 10741615345 16-APR-08

/oracle/oradata/orcl/archivelog/1_100.dbf

10741615345 10741649195 14-MAY-08

/oracle/oradata/orcl/archivelog/1_101.dbf

10741649195 10741754762 15-MAY-08

/oracle/oradata/orcl/archivelog/1_102.dbf

10741754762 10741972765 19-MAY-08

/oracle/oradata/orcl/archivelog/1_103.dbf

10741972765 10742805005 26-MAY-08

ctoc@ORCL> select dbms_flashback.get_system_change_number fscn from dual;

FSCN

------------

10743479803

ctoc@ORCL> create table esms_recov as select * from esms where 1=0;

Table created.

ctoc@ORCL> select count(*) from esms as of scn 10743472211;

COUNT(*)

----------

207

ctoc@ORCL> insert into esms_recov select * from esms as of scn 10743472211;

207 rows created.

ctoc@ORCL> commit;

Commit complete.

ctoc@ORCL> select count(*) from esms;

COUNT(*)

----------

0

ctoc@ORCL> select count(*) from esms_recov;

COUNT(*)

----------

207

ctoc@ORCL> rename esms to esms_old;

Table renamed.

ctoc@ORCL> rename esms_recov to esms;

Table renamed.

ctoc@ORCL> select count(*) from esms;

COUNT(*)

----------

207

至些删除的数据数据全部恢复完成.

[原创]:oracle9i delete后的恢复实验(一)--Flashback Query