Script to know the top sessions and locks

col machine for a40

SELECT * FROM (SELECT TO_CHAR (s.logon_time,’mm/dd hh:mi:ssAM’) loggedon, s.sid, s.status,FLOOR (last_call_et / 60)
“Last Call Elapsed Time (m)”, s.username, s.osuser, p.spid, s.module || ‘ – ‘ || s.program uprogram, s.machine,
s.sql_hash_value FROM v$session s, v$process p WHERE p.addr = s.paddr AND s.TYPE = ‘USER’ AND s.username IS
NOT NULL AND s.status = ‘ACTIVE’ AND last_call_et > 0 ORDER BY 4 DESC) WHERE ROWNUM < 11;

select /*+RULE */ s.sid, s.serial#, p.spid, s.process, substr(s.module,1,20) module, to_char(sysdate – s.last_call_et/60/60/24,’DD-MON-YYYY HH24:MI’) last_call,
decode(block ,0,’NO’,’YES’) BLOCKER, decode(request,0,’NO’,’YES’) WAITER from
v$lock l,v$session s, v$process p where (l.request > 0 or l.block > 0 )
and s.sid = l.sid and p.addr = s.paddr order by last_call_et desc;

  • July 17, 2017 | 14 views
  • Comments