Active Sessions SQL

SQL to return User Sessions that are holding row/table locks:

-- Lists Orauser, Command, Program, Process ID, Session Locks

SELECT
   substr(a.name,1,24) name,
   substr(s.program,1,30) program,
   p.spid SPID, 
   s.osuser,
   l.SID SID,
   s.process PID, 
   s.TERMINAL, 
   S.STATUS
FROM
   sys.dbms_lock_allocated a,
   v$lock l,
   v$session s,
   v$process p
WHERE
   a.lockid = l.id1 and
   l.type = 'UL' and
   l.sid = s.sid and
   p.addr = s.paddr
   order by osuser;

Related:
List all Sessions SQL


© Copyright SS64.com 1999-2014
Some rights reserved