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 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