Cheeky Cockatoo

Life Advice and Stories, Travel, Oracle Tech, Good Food and More

user_rbs_objs.sql

Download Script

column "ID" format 99
column "Segment" format a8
column "User" format a12
column "OS User" format a8
column "Host" format a9
column "Slot" format a4
column "Ext" format a3
column "F PID" format a5 
column "S PID" format a5
column "Slot" format a4
column "Seq" format a6
column "SID" format a10
column "Obj" format a14
column "Time" format a5
column "TAR#" format a11
set pagesize 500
break on "ID" skip 1

select c.usn "ID", 
       c.name "Segment", 
       to_char(to_date(d.start_time,'MM/DD/YYYY HH24:MI:SS'),'HH24:MI') "Time",
       b.sid || ',' || b.serial# "SID",
       b.osuser "OS User",
       b.process "F PID",
       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
and   b.osuser = '&1'
order by c.usn, d.start_time
/
ttitle off
clear breaks