DOYENSYS Knowledge Portal

We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal

Tuesday, July 18, 2017

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;

No comments: