flashback六大技術之flashback transaction query 和flashback table

環境：

15:25:40 [email protected] (^ω^) select * from v$version where rownum=1;

BANNER
-------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
           

    Ⅰ）flashback transaction query

           flashback transaction query是基于flashback version query的“恢複”,因為在flashback version query中，有個僞列versions_xid，這是這兩類query的“紅線”，視圖flashback_transaction_query進行的是全表掃描，要注意代價的評估。

14:17:18 [email protected] (^ω^) desc flashback_transaction_query
 名稱                                      是否為空? 類型
 ----------------------------------------- -------- ----------------------------
 XID                                                RAW(8)
 START_SCN                                          NUMBER
 START_TIMESTAMP                                    DATE
 COMMIT_SCN                                         NUMBER
 COMMIT_TIMESTAMP                                   DATE
 LOGON_USER                                         VARCHAR2(30)
 UNDO_CHANGE#                                       NUMBER
 OPERATION                                          VARCHAR2(32)
 TABLE_NAME                                         VARCHAR2(256)
 TABLE_OWNER                                        VARCHAR2(32)
 ROW_ID                                             VARCHAR2(19)
 UNDO_SQL                                           VARCHAR2(4000)
           

        實驗：

select operation,undo_sql
            from flashback_transaction_query q where q.xid in (
          select versions_xid from t1 versions between scn 4198407 and 4198441)
/

15:34:17 [email protected] (^ω^) select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 4197364

15:34:22 [email protected] (^ω^) delete test where rownum=1;

已删除 1 行。

15:34:41 [email protected] (^ω^) commit;

送出完成。


15:35:08 [email protected] (^ω^) select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 4197405

15:35:25 [email protected] (^ω^) select xid,commit_scn,operation,undo_sql
15:36:16   2              from flashback_transaction_query q where q.xid in (
15:36:16   3            select versions_xid from test versions between scn 4197364 and 4197405)
15:36:18   4  /

XID              COMMIT_SCN
---------------- ----------
OPERATION
----------------------------------------------------------------
UNDO_SQL
--------------------------------------------------------------------------------
06002000F4040000    4197376
DELETE
insert into "HR"."TEST"("ID1","ID2") values ('2281','2283');

06002000F4040000    4197376
           

***************************************************我是分隔線哦********************************************************************

     Ⅱ）flashback table

            由于flashback table使用了DML(注意：不能将表恢複到改變表結構的DDL操作之前)操作去恢複資料，不能保證rowid不變，是以在flashback table之前需要啟用row movement特性。

15:57:19 [email protected] (^ω^) select row_movement from user_tables where table_name='T1';

ROW_MOVEMENT
----------------
DISABLED

16:20:38 [email protected] (^ω^) alter table t1 enable row movement;

表已更改。

16:21:04 [email protected] (^ω^) select row_movement from user_tables where table_name='T1';

ROW_MOVEMENT
----------------
ENABLED


16:21:09 [email protected] (^ω^) select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 4199918

16:22:52 [email protected] (^ω^) select count(*) from t1;

  COUNT(*)
----------
         8

16:23:22 [email protected] (^ω^) delete t1 where rownum=1;

已删除 1 行。

16:23:31 [email protected] (^ω^) commit;

送出完成。

16:23:36 [email protected] (^ω^) flashback table t1 to scn 4199918;

閃回完成。

16:24:19 [email protected] (^ω^) select count(*) from t1;

  COUNT(*)
----------
         8