[Back To HomePage and Script Library]

Disclaimer: Use these scripts and/or any recommendations they may contain at your own risk. These scripts may or may not have been tested.

Title: Rollback Segment User/Transaction

Author: Shaleen Garg, a DBA for DIRECTV in Torrance, California.

Sometimes it is very crucial to know that which rollback segment is being used by which user/transaction. This script provides
that information.
Source/Text/Comments


set pagesize 1000

set echo on

column rbs format a6

column oracle_user format a12 heading "Oracle|Username"

column sid format 99999 heading "Oracle|SID"

column serial# format 99999 heading "Oracle|Serial#"

column unix_pid format a6 Heading "Unix|PID"

column Client_User format a12 Heading "Client|Username"

column Unix_user format a12 Heading "Unix|Username"

select r.name rbs,

       nvl(s.username, 'None') oracle_user,

       s.osuser  client_user,

       p.username unix_user,

       s.sid   sid,

       s.serial# serial#,

       p.spid    unix_pid

from v$process p, v$rollname r, v$session s,v$transaction t

where s.taddr=t.addr

and  s.paddr = p.addr(+)

and  r.usn=t.xidusn(+)

order by r.name;

exit