该视图的定义为：
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语句我们可以撤销该事物。
-----
这篇 【
通过Oracle10g的flashback transaction query新特性进行事务撤销】来自
www.eygle.com |
CSDN技术网摘|
del.icio.us|
365Key
