ITPub博客

首页 > 数据库 > Oracle > 在Oracle 10gR2中设定指定的恢复点

在Oracle 10gR2中设定指定的恢复点

原创 Oracle 作者:tonywi888 时间:2007-11-09 21:17:56 0 删除 编辑

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@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8570952/viewspace-981501/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-02-25

  • 博文量
    159
  • 访问量
    713169