flashback六大技术之flashback transaction query 和flashback t
环境: 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 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 ------------------------ 4197364 15: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 ------------------------ 4197405 15:35:25 hr@ORCL (^ω^) 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 hr@ORCL (^ω^) select row_movement from user_tables where table_name='T1'; ROW_MOVEMENT ---------------- DISABLED 16: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 ---------------- ENABLED 16:21:09 hr@ORCL (^ω^) select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------ 4199918 16:22:52 hr@ORCL (^ω^) select count(*) from t1; COUNT(*) ---------- 8 16: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 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |