ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 数据库基于版本的闪回

数据库基于版本的闪回

原创 Linux操作系统 作者:YallonKing 时间:2012-04-04 16:30:19 0 删除 编辑
情景描述:用户对表误进行了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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 闪回误删除的表
请登录后发表评论 登录
全部评论

注册时间:2011-08-07

  • 博文量
    72
  • 访问量
    248644