Checking statements for dead transactions in 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<---- The value does not decrease.  
39 0 502 ACTIVE NONE 1 
43 45 480 ACTIVE NONE 0

Queries for v $px_session and v $fast_start_servers show many parallel processes in rollback. Based on past engineering experience:

So it was changed to

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

After that, run again

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<--- The value keeps getting smaller.  
35 29 502 ACTIVE NONE 1

Use the following script to view the estimated time (in days) when the rollback is completed:

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 hours. That is, the rollback is expected to be completed in 5.04 hours.

Additional note: When used in other environments, please pay attention to replacing KTUXEUSN=13 and KTUXESLT=5