Script to show Database Sessions using Rollback Segments


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

REM
REM || Title : show_rbs_act.sql
REM ||
REM || Purpose : Display database sessions using rollback segments
REM ||
REM ||
REM || Release No : 1
REM ||
REM || Variables : None
REM ||
REM || Schema : system
REM ||
REM ||
REM ||


COLUMN rbs FORMAT A5 TRUNC
COLUMN sid FORMAT 9990
COLUMN user FORMAT A10 TRUNC
COLUMN command FORMAT A78 TRUNC
COLUMN status FORMAT A6 TRUNC

SELECT r.name "RBS",
s.sid,
s.serial#,
s.username "USER",
t.status,
t.cr_get,
t.phy_io,
t.used_ublk,
t.noundo,
SUBSTR (s.program, 1, 78) "COMMAND"
FROM sys.v_$session s,
sys.v_$transaction t,
sys.v_$rollname r
WHERE t.addr = s.taddr
AND t.xidusn = r.usn
ORDER BY t.cr_get,
t.phy_io

SPOOL show_rbs_act.lis
/
SPOOL off
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.