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

环境：

[sql]

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进行的是全表扫描，要注意代价的评估。 www.2cto.com

[sql]

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)

实验：

[sql]

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;

www.2cto.com

已删除 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 www.2cto.com

----------------------------------------------------------------

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特性。

[sql]

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;

表已更改。