利用flashback transaction query新特性进行事务撤销
具备了flashback version query查询的基础，我们就可以进行基于flashback version query的恢复.
这就是flashback transaction query。flashback transaction query可以从FLASHBACK_TRANSACTION_QUERY中获得指定事务的历史信息以及Undo_SQL，通过这个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来恢复事务。 flashback version query参考:Oracle10g的Flashback version Query 1.通过flashback version query获得XID
EYGLE on 30-MAR-05 >select versions_starttime, versions_endtime, versions_xid,
2 versions_operation, username,user_id
3 from t versions between timestamp minvalue and maxvalue
4 /
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V USERNAME USER_ID
------------------------------ ------------------------------ ---------------- - ---------- ----------
30-MAR-05 09.34.49 AM 000A000B000000F1 D DBSNMP 22
30-MAR-05 09.34.49 AM 000A000B000000F1 D WMSYS 23
30-MAR-05 09.34.49 AM 000A000B000000F1 D OPERATOR 31
30-MAR-05 09.34.49 AM 000A000B000000F1 D TRANS 27
30-MAR-05 09.34.49 AM 000A000B000000F1 D DIP 19
30-MAR-05 09.34.49 AM 000A000B000000F1 D SCOTT 29
30-MAR-05 09.34.49 AM 000A000B000000F1 D TEST 25
30-MAR-05 09.34.15 AM 0001001900000F0F U EYGLE 1
30-MAR-05 09.33.51 AM 00080016000000EF D TEST1 28
30-MAR-05 09.33.23 AM 0004000A000005EF D OUTLN 11
SYSTEM 5
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V USERNAME USER_ID
------------------------------ ------------------------------ ---------------- - ---------- ----------
SYS 0
30-MAR-05 09.34.49 AM TEST 25
30-MAR-05 09.34.15 AM EYGLE 26
30-MAR-05 09.34.49 AM SCOTT 29
30-MAR-05 09.34.49 AM DIP 19
30-MAR-05 09.34.49 AM TRANS 27
30-MAR-05 09.33.51 AM TEST1 28
30-MAR-05 09.34.49 AM OPERATOR 31
30-MAR-05 09.34.49 AM WMSYS 23
30-MAR-05 09.34.49 AM DBSNMP 22
30-MAR-05 09.33.23 AM OUTLN 11
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V USERNAME USER_ID
------------------------------ ------------------------------ ---------------- - ---------- ----------
30-MAR-05 09.49.24 AM 00080006000000EF I PENNY 2
23 rows selected.
2.恢复XID=000A000B000000F1的事务 注意:观察到x$ktuqqry的查询非常耗时，所以请注意评估你的恢复成本。 由于x$ktuqqry表的xid字段上不存在索引(很奇怪，而且我们不能自己添加，估计后续版本中会有改进)，查询该表会导致犬表扫描。 以下测试中，x$ktuqqry中存在大约19万记录，查询一次需要近6分钟。
SYS AS SYSDBA on 30-MAR-05 >select count(addr) from x$ktuqqry;
COUNT(ADDR)
-----------
196015
SYS AS SYSDBA on 30-MAR-05 >set autotrace on
SYS AS SYSDBA on 30-MAR-05 >SELECT /*+ rule */ UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY
2 WHERE XID = '000A000B000000F1';
UNDO_SQL
--------------------------------------------------------------------------------
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('DBSNMP','22');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('WMSYS','23');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('OPERATOR','31');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('TRANS','27');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('DIP','19');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('SCOTT','29');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('TEST','25');
8 rows selected.
Elapsed: 00:05:53.44
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 FIXED TABLE (FULL) OF 'X$KTUQQRY' (TABLE (FIXED))
Statistics
----------------------------------------------------------
393399 recursive calls
0 db block gets
1559016 consistent gets
4388 physical reads
0 redo size
1069 bytes sent via SQL*Net to client
664 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
23164 sorts (memory)
0 sorts (disk)
8 rows processed
SYS AS SYSDBA on 30-MAR-05 >SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY
2 WHERE XID = '000A000B000000F1';
UNDO_SQL
--------------------------------------------------------------------------------
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('DBSNMP','22');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('WMSYS','23');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('OPERATOR','31');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('TRANS','27');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('DIP','19');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('SCOTT','29');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('TEST','25');
8 rows selected.
Elapsed: 00:05:55.30
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=25 Card=1 Bytes=2008)
1 0 FIXED TABLE (FULL) OF 'X$KTUQQRY' (TABLE (FIXED)) (Cost=25 Card=1 Bytes=2008)
Statistics
----------------------------------------------------------
393454 recursive calls
0 db block gets
1562425 consistent gets
4644 physical reads
0 redo size
1069 bytes sent via SQL*Net to client
664 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
23166 sorts (memory)
0 sorts (disk)
8 rows processed
SYS AS SYSDBA on 30-MAR-05 >
3.通过UNDO语句我们可以撤销该事物。 4.注意，在Oracle Database 11g中，需要启用supplemental log data才能够得到UNDO SQL。 以下测试过程供参考：
SQL> connect / as sysdba
Connected.
SQL> alter database add supplemental log data;
Database altered.
SQL> connect eygle/eygle
Connected.
SQL> create table t as select username from dba_users where rownum < 6;
Table created.
SQL> select * from t;
USERNAME
------------------------------
SYS
SYSTEM
OUTLN
DIP
ORACLE_OCM
5 rows selected.
SQL> delete from t where username='DIP';
1 row deleted.
SQL> commit;
Commit complete.
SQL> select versions_xid,versions_operation,username from t versions between timestamp minvalue and maxvalue;
VERSIONS_XID V USERNAME
---------------- - ------------------------------
06001400D60C0000 D DIP
SYS
SYSTEM
OUTLN
DIP
ORACLE_OCM
6 rows selected.
SQL> select xid,operation,undo_sql from FLASHBACK_TRANSACTION_QUERY where xid=hextoraw('06001400D60C0000');
XID OPERATION UNDO_SQL
---------------- -------------------------------- ------------------------------------------------------------
06001400D60C0000 DELETE insert into "EYGLE"."T"("USERNAME") values ('DIP');
06001400D60C0000 BEGIN
2 rows selected.
此处的UNDO_SQL显示为两行，其中一条为BEGIN，这是作为事务开始的标记。Oracle隐式的开始一个事务，在Reverse转换时，BEGIN被显示出来，而且在INSERT之后：
SQL> select xid,operation,undo_sql,undo_change# from FLASHBACK_TRANSACTION_QUERY where xid=hextoraw('06001400D60C0000');
XID OPERATION UNDO_SQL UNDO_CHANGE#
---------------- ---------- ------------------------------------------------------------ ------------
06001400D60C0000 DELETE insert into "EYGLE"."T"("USERNAME") values ('DIP'); 1
06001400D60C0000 BEGIN
