在Oracle10gR2中设定指定的恢复点实现轻松闪回

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.