Statements that check 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--------------------------------------------- -----------------------------------          5      47447 ACTIVE           DEAD                        2819919          0        502 ACTIVE           NONE                              1        480 ACTIVE           NONE                              0

sql>/  ktuxeusn   ktuxeslt   ktuxesqn STATUS           FLAGS                      ktuxesiz---------------------------------- ----------------------------------------------          5      47447 ACTIVE           DEAD                        2819919<----This value does not decrease.          0        502 Active           None                              1        480 active           None                              0

Querying V$px_session and V$fast_start_servers, showing very many parallel processes in rollback, based on previous project experience:

Then instead

Sql>alter system set Fast_start_parallel_rollback=false Scope=both;

After that, execute 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----------------------------------------------- ---------------------------------          5      47447 ACTIVE           DEAD                        2033516        502 ACTIVE           NONE                              1 sql>/  ktuxeusn   ktuxeslt   ktuxesqn STATUS           FLAGS                      Ktuxesiz--------------------------------------------------------------------------------          5      47447 ACTIVE           DEAD                        2033433<---This value keeps getting smaller.        502 ACTIVE           NONE                              1

Use the following script, for example, to view the estimated time, in days, for rollback completion:

Sql> set Serveroutput onsql> declare 2   l_start number; 3   l_end number; 4   begin 5   Select Ktuxesiz int o l_startfrom x$ktuxe where ktuxeusn=13 and ktuxeslt=5; 6   dbms_lock.sleep; 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;   /time est day:.21

24*0.21=5.04 hours. That is, the rollback is completed after an estimated 5.04 hours.

Also note: When used in other environments, be careful to replace ktuxeusn=13 and ktuxeslt=5