数据库基于版本的闪回

情景描述：用户对表误进行了update操作。
--查看测试表及数据
SQL> select * from test;

        ID NAME
---------- --------------------
         1 yallonking
         2 yallonking
         3 yallonking
         4 oraking
         5 oraking
         6 oraking

6 rows selected.
--更新表部分数据
SQL> update test set name='afterchange' where name = 'oraking';

3 rows updated.
--查看更新结果
SQL> select * from test;

        ID NAME
---------- --------------------
         1 yallonking
         2 yallonking
         3 yallonking
         4 afterchange
         5 afterchange
         6 afterchange

6 rows selected.
--回滚更新操作
SQL> rollback;

Rollback complete.

SQL> select * from test;

        ID NAME
---------- --------------------
         1 yallonking
         2 yallonking
         3 yallonking
         4 oraking
         5 oraking
         6 oraking

6 rows selected.
--更新部分数据并提交更新操作
SQL> update test set name='afterchange' where name = 'oraking';

3 rows updated.

SQL> commit;

Commit complete.

SQL> rollback;

Rollback complete.

SQL> select * from test;

        ID NAME
---------- --------------------
         1 yallonking
         2 yallonking
         3 yallonking
         4 afterchange
         5 afterchange
         6 afterchange

6 rows selected.
--查看当前时间
SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2012/04/03 22:50:16
--查看当前scn
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
 2912695742
--查看18mins之内对表的所有操作
SQL> col VERSIONS_STARTTIME for a25
SQL> select versions_xid,versions_starttime,versions_endtime,versions_operation from test versions between timestamp

to_date(sysdate - 18/1440) and systimestamp;

VERSIONS_XID     VERSIONS_STARTTIME        VERSIONS_ENDTIME          V
---------------- ------------------------- ------------------------- -
030006006E050000 03-APR-12 10.46.13 PM                               U
030006006E050000 03-APR-12 10.46.13 PM                               U
030006006E050000 03-APR-12 10.46.13 PM                               U

 

                                           03-APR-12 10.46.13 PM
                                           03-APR-12 10.46.13 PM
                                           03-APR-12 10.46.13 PM

9 rows selected.
--根据以上相关操作时间，对表数据进行闪回操作
SQL> alter table test enable row movement;

Table altered.

SQL> flashback table test to timestamp to_timestamp('2012/04/03 22:46:13','yyyy/mm/dd hh24:mi:ss');

Flashback complete.

SQL> select * from test;

        ID NAME
---------- --------------------
         1 yallonking
         2 yallonking
         3 yallonking
         4 oraking
         5 oraking
         6 oraking

6 rows selected.

SQL> alter table test disable row movement;

Table altered.

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

转载于:http://blog.itpub.net/26143577/viewspace-720309/