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;
/
