Script to Show Active (in progress) Transactions


SET ECHO OFF
SET FEEDBACK ON
SET PAGES 80
SET LINES 132

REM
REM || Title : show_active_sess.sql
REM ||
REM || Purpose : Shows active (in progress) transactions
REM ||
REM ||
REM || Release No : 1
REM ||
REM || Variables : None
REM ||
REM || Schema : system
REM ||
REM ||

 

COLUMN name FORMAT A8
COLUMN username FORMAT A8
COLUMN osuser FORMAT A8
COLUMN start_time FORMAT A17
COLUMN status FORMAT A12

TTITLE 'Active transactions'

SELECT username,
terminal,
osuser,
t.start_time,
r.name,
t.used_ublk "ROLLB BLKS",
DECODE (
t.SPACE,
'YES', 'SPACE TX',
DECODE (t.recursive, 'YES', 'RECURSIVE TX', DECODE (t.noundo, 'YES', 'NO UNDO TX', t.status))) status
FROM sys.v_$transaction t,
sys.v_$rollname r,
sys.v_$session s
WHERE t.xidusn = r.usn
AND t.ses_addr = s.saddr

SPOOL show_active_sess.lis
/
SPOOL off


TTITLE OF
SET FEEDBACK ON
This script is provided for educational purposes only. The script has been tested and appears to work as intended. However, you should always test any script before relying on it. No responsibility will be accepted for Lost or damage that may occur from it's use.