DECLARE CURSOR tx IS SELECT /*+ USE_NL(S,T,X) */ NVL (s.username, 'session no more exists or running on the other node of RAC' ), s.sid,x.ktuxeusn, x.ktuxeslt, x.ktuxesqn, x.ktuxesiz FROM ((sys.x$ktuxe x LEFT JOIN sys.v$transaction t ON t.xidusn = x.ktuxeusn AND t.xidslot = x.ktuxeslt AND t.xidsqn = x.ktuxesqn ) LEFT JOIN sys.v$session s ON s.saddr = t.ses_addr) WHERE x.ktuxesta = 'ACTIVE' AND x.ktuxesiz > 1; user_name VARCHAR2 (80); xid_usn NUMBER; xid_slot NUMBER; xid_sqn NUMBER; used_ublk1 NUMBER; used_ublk2 NUMBER; sid number; BEGIN OPEN tx; LOOP FETCH tx INTO user_name, sid, xid_usn, xid_slot, xid_sqn, used_ublk1; EXIT WHEN tx%NOTFOUND; IF tx%ROWCOUNT = 1 THEN sys.DBMS_LOCK.sleep (120); SELECT SUM (ktuxesiz) INTO used_ublk2 FROM sys.x$ktuxe WHERE ktuxeusn = xid_usn AND ktuxeslt = xid_slot AND ktuxesqn = xid_sqn AND ktuxesta = 'ACTIVE'; sys.DBMS_OUTPUT.put_line ('session (' || user_name || sid ||')'); sys.DBMS_OUTPUT.put_line ( 'transaction ' || xid_usn || '.' || xid_slot || '.' || xid_sqn || ' will finish rolling back at approximately ' || TO_CHAR (sysdate + used_ublk2 / (used_ublk2 - used_ublk1) / 30 / 24, 'HH24:MI:SS DD-MON-YYYY' ) ); END IF; END LOOP; IF user_name IS NULL THEN sys.DBMS_OUTPUT.put_line ('No transactions appear to be rolling back.'); END IF; END; /