資料庫基於版本的閃回

YallonKing發表於2012-04-04
資料庫
情景描述：使用者對錶誤進行了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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章