Cheeky Cockatoo

column "Segment" format a10
column "OS_User" format a12 heading "Username"
column "Host"    format a9
column "F PID"   format a5 
column "S PID"   format a5
column "SIDS"    format a9  heading "SID|Serial#"
column "Obj"     format a16 trunc
column "Time"    format a5
column "TAR#"    format a11
set space 1
set pagesize 500
set lines 84
break on "Segment" skip 1

select /*+ rule */
       c.name "Segment", 
       to_char(to_date(d.start_time,'MM/DD/RR HH24:MI:SS'),'HH24:MI') "Time",
       b.sid || ',' || b.serial# "SIDS",
       decode ( b.osuser, 
                'oracle', b.username,
                null,     b.username,
                b.osuser ) "OS_User",
       decode(instr(b.machine, 'phys-'),
              0, b.machine,
              substr ( b.machine, 6 ) ) "Host",
       f.spid "S PID",
       decode ( o.object_name, 
                null, 'Rollback', 
                o.object_name ) "Obj",
       client_info "TAR#"
from  v$lock a, 
      v$session b, 
      v$rollname c, 
      v$transaction d, 
      v$process f,
      dba_objects o
where a.sid=b.sid
and   a.type like 'T_'
and   f.addr=b.paddr
and   b.taddr=d.addr
and   c.usn=d.xidusn
and   o.object_id (+) = a.id1
order by c.usn, d.start_time
/
ttitle off
rem clear breaks