flashback transaction query

flashback transaction query其实是flashback version query查询的一个扩充，flashback version query我们说明了我们可以审计一段时间内表的所有改变，但是也仅仅是能发现问题，对于错误的事务，没有好的处理办法。但是flashback transaction query提供了我们办法，我们可以从FLASHBACK_TRANSACTION_QUERY中获得事务的历史以及Undo_sql，也就是说，我们审计一个事务到底做了什么甚至可以回滚一个已经提交的事务。

flashback transaction query需要用到FLASHBACK_TRANSACTION_QUERY视图，我们先看一下视图
SQL> desc FLASHBACK_TRANSACTION_QUERY;
Name Type Nullable Default Comments
---------------- -------------- -------- ------- --------------------------
XID RAW(8) Y Transaction identifier
START_SCN NUMBER Y Transaction start SCN
START_TIMESTAMP DATE Y Transaction start timestamp
COMMIT_SCN NUMBER Y Transaction commit SCN
COMMIT_TIMESTAMP DATE Y Transaction commit timestamp
LOGON_USER VARCHAR2(30) Y Logon user for transaction
UNDO_CHANGE# NUMBER Y 1-based undo change number
OPERATION VARCHAR2(32) Y forward operation for this undo
TABLE_NAME VARCHAR2(256) Y table name to which this undo applies
TABLE_OWNER VARCHAR2(32) Y owner of table to which this undo applies
ROW_ID VARCHAR2(19) Y rowid to which this undo applies
UNDO_SQL VARCHAR2(4000) Y SQL corresponding to this undo
其定义为：
select xid, start_scn, start_timestamp,
decode(commit_scn, 0, commit_scn, 281474976710655, NULL, commit_scn)
commit_scn, commit_timestamp,
logon_user, undo_change#, operation, table_name, table_owner,
row_id, undo_sql
from sys.x$ktuqqry

好，现在，我们试着回滚一个已经提交的事务。
其实，需要从FLASHBACK_TRANSACTION_QUERY获得数据，关键问题是获得事务XID，
SQL> create table test as select * from all_objects;
Table created.

SQL> set time on
11:15:48 SQL> delete from test where rownum <=10;
10 rows deleted.

11:16:10 SQL> select xid from v$transaction;
XID
----------------
04001200AE010000

11:20:09 SQL> commit;
Commit complete.

当然，我们在测试中，可以在事务没有提交的时候，获得事务的04001200AE010000。
实际情况下，我们不可能去跟踪每个事务，想要获得已提交事务的XID，就必须通过flashback version query了，如
11:30:32 SQL> select versions_xid, versions_operation
11:30:41 2 from test versions between timestamp
11:30:41 3 to_date(’2004-04-08 11:15:48’,’yyyy-mm-dd hh24:mi:ss’)
11:30:41 4 and MAXVALUE
11:30:41 5 WHERE versions_xid is not null
11:30:41 6 order by VERSIONS_STARTTIME;
VERSIONS_XID V
---------------- -
04001200AE010000 D
04001200AE010000 D
04001200AE010000 D
04001200AE010000 D
04001200AE010000 D
04001200AE010000 D
04001200AE010000 D
04001200AE010000 D
04001200AE010000 D
04001200AE010000 D
10 rows selected.

可以看到，我们共删除了10行，对应的是10个操作。这10个操作都是同一个事务，我们现在利用这个XID来获得UNDO_SQL。

SQL>SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY
WHERE XID = ’04001200AE010000’;
UNDO_SQL
------------------------------------------------------------------------------
insert into "TEST"."TEST"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DA
insert into "TEST"."TEST"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DA
insert into "TEST"."TEST"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DA
insert into "TEST"."TEST"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DA
insert into "TEST"."TEST"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DA
insert into "TEST"."TEST"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DA
insert into "TEST"."TEST"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DA
insert into "TEST"."TEST"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DA
insert into "TEST"."TEST"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DA
insert into "TEST"."TEST"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DA

这个删除语句对应的是10个insert语句，如果我们想回滚这个事务，我们执行这10个insert语句即可（以上语句都是完整的语句，这里因为显示问题，我就不完全显示了）。

可以看到，flashback transaction query主要用于审计一个事务，并可以回滚一个已经提交的事务。如果确定出错的事务是最后一个事务，我们利用falshback table或者flashback query就可以解决问题，但是，如果我们执行了一个错误的事务之后，又执行了一系列正确的事务，那么。利用flashback transaction query，我们甚至可以是回滚这个错误的事务。

本文转自
http://www.itpub.net/thread-213225-1-1.html