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

作者: jcmatio  出自:

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

数据库版本：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

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