【Flashback】Flashback Query功能实践

2020-02-05 19:39:05

下面的实验是在10g 10.2.0.4环境中完成的，体验Oracle带给我们的Flashback Query功能。

Flashback query的SQL语句语法如下：
①按照SCN号进行闪回的语法如下
SELECT * FROM TABLENAME AS OF SCN SCN_NUMBER WHERE ……;
②按照具体时间进行闪回的语法如下
SELECT * FROM TABLENAME AS OF TIMESTAMP TO_TIMESTAMP('2011-05-26 21:06:06', 'YYYY-MM-DD HH:MI:SS') WHERE ……;

1.创建测使用表test_flashback_query
sec@ora10g> create table test_flashback_query as select * from dba_objects;

Table created.

2.查询当前表中的记录数
sec@ora10g> select count(*) from test_flashback_query;

COUNT(*)
----------
     11483

3.获得当前的SCN号
sec@ora10g> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 1245522

4.模拟删除整张表的内容
sec@ora10g> delete from test_flashback_query;

11483 rows deleted.

sec@ora10g> commit;

Commit complete.

5.确定测试表记录已经被全部删除
sec@ora10g> SELECT count(*) FROM test_flashback_query;

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

6.神奇的闪回效果即将出现，下面是使用SCN和时间的方式进行闪回查询的结果，数据果然找回来了。
test@sec> SELECT count(*) FROM test_flashback_query as of scn 1245522;

COUNT(*)
----------
     11524

sec@ora10g> select OWNER,OBJECT_NAME FROM test_flashback_query as of scn 1245522 where rownum<3;

OWNER                          OBJECT_NAME
------------------------------ --------------------
SYS                            ICOL$
SYS                            I_USER1

sec@ora10g> SELECT count(*) from test_flashback_query AS OF TIMESTAMP TO_TIMESTAMP('2011-05-26 21:44:50','YYYY-MM-DD HH:MI:SS');

COUNT(*)
----------
     11524

sec@ora10g> select OWNER,OBJECT_NAME FROM test_flashback_query AS OF TIMESTAMP TO_TIMESTAMP('2011-05-26 21:44:50','YYYY-MM-DD HH:MI:SS') where rownum<3;

OWNER                          OBJECT_NAME
------------------------------ --------------------
SYS                            ICOL$
SYS                            I_USER1

7.小结
通过上面的Flashback Query闪回查询方法可以查询到该表被删除之前状态。因此在知道误删除数据时的SCN或者的时间时后，可以使用这个方法大大简化数据恢复过程，争取宝贵的时间。

Good luck.

secooler
11.05.26

-- The End --