Oracle中死事务的检查语句

sql> select ktuxeusn, ktuxeslt, ktuxesqn, /* transaction id */ 
2 ktuxesta status, ktuxecfl flags ,ktuxesiz 
3 from x$ktuxe 
4 where ktuxesta!='inactive'; 

ktuxeusn ktuxeslt ktuxesqn status flags ktuxesiz 
---------- ---------- ---------- ---------------- ------------------------ ---------- 
13 5 47447 active dead 2819919 
39 0 502 active none 1 
43 45 480 active none 0

sql> / 

ktuxeusn ktuxeslt ktuxesqn status flags ktuxesiz 
---------- ---------- ---------- ---------------- ------------------------ ---------- 
13 5 47447 active dead 2819919<----该值没减小。 
39 0 502 active none 1 
43 45 480 active none 0

查询v$px_session和v$fast_start_servers,显示很多并行进程在rollback，根据以往的工程经验：

于是改为

sql>alter system set fast_start_parallel_rollback=false scope=both;

之后，再次运行

sql> select ktuxeusn, ktuxeslt, ktuxesqn,/* transaction id */ 
2 ktuxesta status, ktuxecfl flags ,ktuxesiz 
3 from x$ktuxe 
4 where ktuxesta!='inactive'; 
ktuxeusn ktuxeslt ktuxesqn status flags ktuxesiz 
---------- ---------- -------------------------- ------------------------ ---------- 
13 5 47447 active dead 2033516 
35 29 502 active none 1 

sql> / 

ktuxeusn ktuxeslt ktuxesqn status flags ktuxesiz 
---------- ---------- -------------------------- ------------------------ ---------- 
13 5 47447 active dead 2033433<---该值不断变小。 
35 29 502 active none 1

使用如下脚本查看回滚完毕的预计时间（以天为单位）：

sql> set serveroutput on 
sql> declare 
2 l_start number; 
3 l_end number; 
4 begin 
5 select ktuxesiz into l_startfrom x$ktuxe where ktuxeusn=13 and ktuxeslt=5; 
6 dbms_lock.sleep(60); 
7 select ktuxesiz into l_endfrom x$ktuxe where ktuxeusn=13 and ktuxeslt=5; 
8 dbms_output.put_line('time estday:'|| round(l_end/(l_start -l_end)/60/24,2)); 
9 end; 
10 / 
time est day:.21

24*0.21=5.04小时。即：预计5.04小时后回滚完毕。

另外注意：在其他环境使用时，请注意替换ktuxeusn=13和ktuxeslt=5