下面的實驗是在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 --

【Flashback】Flashback Query功能實踐

相關文章