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

环境：

15:25:40 hr@ORCL (^ω^) 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 sys@ORCL (^ω^) 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 hr@ORCL (^ω^) select dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER------------------------                 419736415:34:22 hr@ORCL (^ω^) delete test where rownum=1;已删除 1 行。15:34:41 hr@ORCL (^ω^) commit;提交完成。15:35:08 hr@ORCL (^ω^) select dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER------------------------                 419740515:35:25 hr@ORCL (^ω^) select xid,commit_scn,operation,undo_sql15: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    4197376DELETEinsert into "HR"."TEST"("ID1","ID2") values ('2281','2283');06002000F4040000    4197376

***************************************************我是分隔线哦********************************************************************

Ⅱ）flashback table

由于flashback table使用了DML(注意：不能将表恢复到改变表结构的DDL操作之前)操作去恢复数据，不能保证rowid不变，所以在flashback table之前需要启用row movement特性。

15:57:19 hr@ORCL (^ω^) select row_movement from user_tables where table_name='T1';ROW_MOVEMENT----------------DISABLED16:20:38 hr@ORCL (^ω^) alter table t1 enable row movement;表已更改。16:21:04 hr@ORCL (^ω^) select row_movement from user_tables where table_name='T1';ROW_MOVEMENT----------------ENABLED16:21:09 hr@ORCL (^ω^) select dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER------------------------                 419991816:22:52 hr@ORCL (^ω^) select count(*) from t1;  COUNT(*)----------         816:23:22 hr@ORCL (^ω^) delete t1 where rownum=1;已删除 1 行。16:23:31 hr@ORCL (^ω^) commit;提交完成。16:23:36 hr@ORCL (^ω^) flashback table t1 to scn 4199918;闪回完成。16:24:19 hr@ORCL (^ω^) select count(*) from t1;  COUNT(*)----------         8