This script displays active transactions (by user and terminal) in rollback
segments.

Execution Environment:
     SQL, SQL*Plus

Access Privileges:
     Requires DBA access privileges to be executed.

Usage:
     sqlplus user/

Instructions:
     Copy the script to a file and execute it from SQL*Plus.

PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text 
editors, e-mail packages, and operating systems handle text formatting (spaces, 
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected.

The following is a short script which returns the username, OS login name and  
terminal from the dynamic performance tables for any user with a lock on a  
rollback segment. 

SET ECHO off 
REM NAME:   TFSATRBS>SQL 
REM USAGE:"@path/tfsatrbs" 
REM -------------------------------------------------------------------------- 
REM REQUIREMENTS: 
REM SELECT privileges on v$rollback tables 
REM -------------------------------------------------------------------------- 
REM PURPOSE: 
REM    The purpose of this script is to show the current holders of locks 
REM    in rollback segments.  The script will display both Oracle username 
REM    and the OS username for each lock. 
REM -------------------------------------------------------------------------- 
REM Main text of script follows: 
 
 
column rr heading 'RB Segment' format a18 
column us heading 'Username' format a15 
column os heading 'OS User' format a10 
column te heading 'Terminal' format a10 
 
 
SELECT     r.name rr,  
           nvl(s.username,'no transaction') us, 
           s.osuser os,  
           s.terminal te 
FROM       v$lock  l, v$session  s,v$rollname  r 
WHERE      l.sid = s.sid(+) AND  
           trunc(l.id1/65536) = r.usn AND 
           l.type = 'TX' AND  
    l.lmode = 6 
ORDER BY   r.name 
/ 
 
 
 
==============
Sample Output: 
==============

 
RB Segment      Username        OS User     Terminal 
---------------	---------------	----------  ---------- 
R01             SCOTT           usupport    ttyq1

EXCEPT WHERE EXPRESSLY PROVI
DED OTHERWISE, THE INFORMATION, SOFTWARE,
PROVIDED ON AN "AS IS" AND "AS AVAILABLE" BASIS. ORACLE EXPR
ESSLY DISCLAIMS
ALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
PURPOSE AND NON-INFRINGEMENT. ORACLE MAKES NO WARRANTY THAT: (A) THE RESULTS
THAT MAY BE OBTAINED FROM THE USE OF THE SOFTWARE WILL BE ACCURATE OR
RELIABLE; OR (B) THE INFORMATION, OR OTHER MATERIAL OBTAINED WILL MEET YOUR
EXPECTATIONS. ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE DOWNLOADED OR
OTHERWISE OBTAINED IS DONE AT YOUR OWN DISCRETION AND RISK. ORACLE SHALL HAVE
NO RESPONSIBILITY FOR ANY DAMAGE TO YOUR COMPUTER SYSTEM OR LOSS OF DATA THAT
RESULTS FROM THE DOWNLOAD OF ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE.

ORACLE RESERVES THE RIGHT TO MAKE CHANGES OR UPDATES TO THE SOFTWARE AT ANY
TIME WITHOUT NOTICE.

IN NO EVENT SHALL ORACLE BE 
LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
SPECIAL OR CONSEQUENTIAL DAMAGES, OR DAMAGES FOR LOSS OF PROFITS, REVENUE,
DATA OR USE, INCURRED BY YOU OR ANY THIRD PARTY, WHETHER IN AN ACTION IN
CONTRACT OR TORT, ARISING FROM YOUR ACCESS TO, OR USE OF, THE SOFTWARE.

SOME JURISDICTIONS DO NOT ALLOW THE LIMITATION OR EXCLUSION OF LIABILITY.
ACCORDINGLY, SOME OF THE ABOVE LIMITATIONS MAY NOT APPLY TO YOU.