豆豆网 > 技术应用 > 数据库 > Oracle > 正文

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

http://tech.ddvip.com 2007年05月06日社区交流

关键字：触发器语法 Oracle物化视图跟踪sql语句密码丢失数据仓储 Oracle 9i个人版

本文详细介绍在Oracle 10gR2中设定指定的恢复点实现轻松闪回

　　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

12 下一页

责编：豆豆技术应用

点击发表评论

正在加载评论...