rbs_user_objs.sql
Download Script
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 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 b.username = '&username'
and o.object_id (+) = a.id1
order by c.usn, d.start_time
/
ttitle off
rem clear breaks