使用Oracle9i的Flashback Query功能找回误删除的数据

一个闪回查询实验的例子：

数据库版本：Oracle 9.2.0.4

操作系统：linux AS4

进行闪回查询必须设置自动回滚段管理，在init.ora设置参数UNDO_MANAGEMENT=AUTO，参数undo_retention=n，决定了能往前闪回的最大时间，值越大就需要越多Undo空间。

# sqlplus test/test

SQL*Plus: Release 9.2.0.4.0 - Production on Fri Jul 22 11:27:19 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL>  select dbms_flashback.get_system_change_number fscn from dual; (查询当前的SCN号)


      FSCN
----------
897271357

SQL> select count(*) from customer; (查询customer表的总大小)

  COUNT(*)
----------
    247064

SQL> select dbms_flashback.get_system_change_number fscn from dual;  (再次查询当前的SCN号)

      FSCN
----------
897271368

SQL> delete customer where rownum<100;    (删除customer表前99行数据)

99 rows deleted.


SQL> commit;

Commit complete.

SQL>  select dbms_flashback.get_system_change_number fscn from dual;  (查询此时当前的SCN号)

      FSCN
----------
897271383

SQL> select count(*) from customer;  (此时customer表数据的大小已变化,发现数据少了前99条)

  COUNT(*)
----------
    246965

SQL> create table customer_bak as select * from customer where 1=0;  (创建一个恢复表)

Table created.

SQL> select count(*) from customer_bak;

  COUNT(*)
----------
         0

SQL> select count(*) from customer as of scn 897271357; (选择SCN向前恢复)

  COUNT(*)
----------
    247064


SQL> select count(*) from customer as of scn 897271368; (尝试更多的SCN,获取最佳值)

  COUNT(*)
----------
    247064

选择恢复到SCN为897271357的时间点:

SQL> insert into customer_bak select * from customer  as of scn 897271357;  


247064 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from customer_bak where rownum <100;

  COUNT(*)
----------
        99

SQL> insert into customer select * from customer_bak where rownum< 100;  (找回前99条记录，插回原表)

99 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from customer;

  COUNT(*)
----------
    247064

至此，闪回恢复成功完成。