在Oracle 10gR2中设定指定的恢复点实现轻松闪回

欢迎进入Oracle社区论坛，与200万技术人员互动交流 >>进入 Oracle href="http://search.e800.com.cn/Oracle" target=_blank alt="Oracle"> Oracle 9i引入的闪回查询功能显示前一个时间点的查询结果。但是，表本身并没有改变。 Oracle 10g 增强了闪回功能，可

欢迎进入Oracle社区论坛，与200万技术人员互动交流 >>进入

Oracle href="http://search.e800.com.cn/Oracle" target=_blank alt="Oracle">Oracle 9i引入的闪回查询功能显示前一个时间点的查询结果。但是，表本身并没有改变。Oracle 10g增强了闪回功能，可以永久闪回一个表格。现在Oracle 10g第二版通过增加指定的恢复点，使这一过程更加方便。

存储

应用恢复点不必知道准确的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。

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

列表A

恢复点存储在控制文件中，所以即使数据库关闭（安装好，但未开），它们仍然有效。这意味着你还可在FLASHBACK DATABASE命令中使用它们。

作者：Bob Watkins(OCP、MCDBA、MCSE、MCT)是一位有25年经验的计算机专业人士，从事过技术培训师、顾问与数据库管理员等职。