使用Oracle9i的Flashback Query功能找回誤刪除的資料

一個閃回查詢實驗的例子：

資料庫版本：Oracle 9.2.0.4

作業系統：Solaris 8

進行閃回查詢必須設定自動復原段管理，在init.ora設定參數UNDO_MANAGEMENT=AUTO，參數undo_retention=n，決定了能往前閃回的最大時間，值越大就需要越多Undo空間。

# sqlplus wacos/oss

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

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

至此，閃回恢複成功完成。