Oracle 9i引入的閃回查詢功能顯示前一個時間點的查詢結果。但是，表本身並沒有改變。Oracle 10g增強了閃回功能，可以永久閃回一個表格。現在Oracle 10g第二版透過增加指定的恢復點，使這一過程更加方便。

通常，你為FLASHBACK TABLE語句提供一個時間標籤或系統改變號（SCN），表明你希望閃回多遠。（閃回次數僅限於系統儲存撤銷的次數。）問題在於，時間標籤只是一個近似值，而瞭解準確的SCN需要查明一個特別的事務。在開始更新前，你可應用DBMS_FLASHBACK中的GET_SYSTEM_CHANGE_NUMBER程式來顯示當前的SCN，但你必須記住它。

應用恢復點不必知道準確的SCN。恢復點建立後，它即恢復當前SCN。因此，使用恢復點名稱等同於使用對應的SCN。

CREATE RESTORE POINT（建立恢復點）命令的語法很簡單：

CREATE RESTORE POINT restore_point_name;

FLASHBACK ANY TABLE或SELECT ANY DICTIONARY許可權需要建立一個正常（無保證）的恢復點。一旦恢復點不再需要，對應的DROP RESTORE POINT命令就將它刪除。

列表A顯示向OE模式提供的正確許可，建立一個恢復點，然後在一個錯過正確WHERE子句的錯誤UPDATE後建立一個FLASHBACK TABLE。

SQL> CONNECT / AS SYSDBA
Connected.
SQL> GRANT SELECT ANY DICTIONARY TO oe; 
Grant succeeded.
SQL> CONNECT OE/OE
Connected.
SQL> -- Tables must have row movement enabled to flash back
SQL> ALTER TABLE warehouses 2 ENABLE ROW MOVEMENT;
Table altered.
SQL> CREATE RESTORE POINT my_update;
Restore point created.
SQL> COLUMN name FORMAT a25
SQL> COLUMN time FORMAT a32
SQL> 
SQL> SELECT name, scn, time, 
guarantee_flashback_database 2 FROMv$restore_point;
NAME SCN TIME GUA 
------------------------- ---------- ------------------------------ 
MY_UPDATE 734934 10-AUG-06 03.32.29.000000000 PM NO 
SQL> UPDATE warehouses 2 SET warehouse_name = 'Bellevue, WA';
9 rows updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT warehouse_id, warehouse_name 2 FROM warehouses;
WAREHOUSE_ID WAREHOUSE_NAME 
------------ ----------------------------------- 
1 Bellevue, WA 2 Bellevue, WA 3 Bellevue, WA 4 Bellevue, 
WA 5 Bellevue, WA 6 Bellevue, WA 7 Bellevue, WA 8 Bellevue,
WA 9 Bellevue, WA 9 rows selected.SQL> CONNECT / AS SYSDBA Connected.
SQL> GRANT SELECT ANY DICTIONARY TO oe;
Grant succeeded.
SQL> CONNECT OE/OE
Connected.
SQL> -- Tables must have row movement enabled to flash back
SQL> ALTER TABLE warehouses 2 ENABLE ROW MOVEMENT;
Table altered.
SQL> CREATE RESTORE POINT my_update;
Restore point created.
SQL> COLUMN name FORMAT a25
SQL> COLUMN time FORMAT a32
SQL> 
SQL> SELECT name, scn, time, 
guarantee_flashback_database 2 FROMv$restore_point;
NAME SCN TIME GUA 
------------------------- ---------- ------------------------------- 
MY_UPDATE 734934 10-AUG-06 03.32.29.000000000 PM NO 
SQL> UPDATE warehouses 2 SET warehouse_name = 'Bellevue, WA';
9 rows updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT warehouse_id, warehouse_name 2 FROM warehouses;
WAREHOUSE_ID WAREHOUSE_NAME 
------------ -----------------------------------
1 Bellevue, WA 2 Bellevue, WA 3 Bellevue, 
WA 4 Bellevue, WA 5 Bellevue, WA 6 Bellevue, WA 7 Bellevue,
WA 8 Bellevue, WA 9 Bellevue, WA 9 rows selected.
SQL> FLASHBACK TABLE warehouses 2 TO RESTORE POINT my_update;
Flashback complete.
SQL> SELECT warehouse_id, warehouse_name 2 FROM warehouses;
WAREHOUSE_ID WAREHOUSE_NAME 
------------ ----------------------------------- 
1 Southlake, Texas 2 San Francisco 3 New Jersey 4 Seattle, 
Washington 5 Toronto 6 Sydney 7 Mexico City 8 Beijing 9 Bombay 
9 rows selected.
SQL> FLASHBACK TABLE warehouses 2 TO RESTORE POINT my_update;
Flashback complete.
SQL> SELECT warehouse_id, warehouse_name 2 FROM warehouses;
WAREHOUSE_ID WAREHOUSE_NAME 
------------ ----------------------------------- 
1 Southlake, Texas 2 San Francisco 3 New Jersey 4 Seattle, 
Washington 5 Toronto 6 Sydney 7 Mexico City 8 Beijing 9 Bombay 
9 rows selected.

列表A

恢復點儲存在控制檔案中，所以即使資料庫關閉（安裝好，但未開），它們仍然有效。這意味著你還可在FLASHBACK DATABASE命令中使用它們。

[@more@]

在Oracle 10gR2中設定指定的恢復點

相關文章