Oracle Current Activity
Given below is a small query that provides the following information about current activity in Oracle database
Which user is currently logged-on?
Which SQL Query are they running?
Which computer the user is logged on from?
How long the query is running?
SELECT
SUBSTR(SS.USERNAME,1,8) USERNAME,
SS.OSUSER “USER”,
AR.MODULE || ‘ @ ‘ || SS.MACHINE CLIENT,
SS.PROCESS PID,
TO_CHAR(AR.LAST_LOAD_TIME, ‘DD-Mon HH24:MM:SS’) LOAD_TIME,
AR.DISK_READS DISK_READS,
AR.BUFFER_GETS BUFFER_GETS,
SUBSTR(SS.LOCKWAIT,1,10) LOCKWAIT,
W.EVENT EVENT,
SS.STATUS,
AR.SQL_fullTEXT SQL
FROM V$SESSION_WAIT W,
V$SQLAREA AR,
V$SESSION SS,
v$TIMER T
WHERE SS.SQL_ADDRESS = AR.ADDRESS
AND SS.SQL_HASH_VALUE = AR.HASH_VALUE
AND SS.SID = W.SID (+)
AND SS.STATUS = ‘ACTIVE’
AND W.EVENT != ‘client message’
ORDER BY SS.LOCKWAIT ASC, SS.USERNAME, AR.DISK_READS DESC
— Pass the SID, get the query that is been run
set verify off
set head off
set feedback on
set long 5000
select distinct
‘=========================================================’||chr(10)||
‘Sid , Serial# : ‘||S.Sid ||’ , ‘||S.Serial# ||Chr(10)||
‘Server/Shadow : ‘||P.Spid ||Chr(10)||
‘Client/Foreground : ‘||S.Process ||Chr(10)||
‘Terminal / Machine: ‘||S.terminal||’ / ‘||S.Machine ||Chr(10)||
‘Username……..: ‘||S.Username ||Chr(10)||
‘Osuser……….: ‘||S.Osuser ||Chr(10)||
‘Program………: ‘||S.Program ||Chr(10)||
‘Module……….: ‘||S.Module ||Chr(10)||
‘Status……….: ‘||S.Status ||Chr(10)||
‘Action……….: ‘||S.Action ||Chr(10)||
‘Wait_time…….: ‘||W.Wait_time ||Chr(10)||
‘State ……….: ‘||W.State ||Chr(10)||
‘Wait Event …..: ‘||W.Event ||Chr(10)||
‘Seconds_in_wait.: ‘||W.Seconds_in_wait ||Chr(10)||
‘Pga_alloc….: ‘|| To_char(P.Pga_alloc_mem/1024/1024,’9,999.99′) ||’ Mb’ ||Chr(10)||
‘Pga_used…..: ‘|| To_char(P.Pga_used_mem/1024/1024 ,’9,999.99′) ||’ Mb’ ||Chr(10)||
‘Pga_free…..: ‘|| To_char(P.Pga_freeable_mem/1024/1024,’9,999.99′) ||’ Mb’ ||Chr(10)||
‘Pga_max……: ‘|| To_char(P.Pga_max_mem/1024/1024,’9,999.99′) ||’ Mb’ ||Chr(10)||
‘Lock / Latch.: ‘|| Nvl(S.Lockwait, ‘None’)||’/ ‘||Nvl(P.Latchwait, ‘None’) ||Chr(10)||
‘Blocking Sessi=on: ‘||s.blocking_session||Chr(10)||
‘Blocking Session Status: ‘|| s.blocking_session_status||Chr(10)||
‘Latch Spin…: ‘|| Nvl(P.Latchspin, ‘None’) ||Chr(10)||
‘Logon Time…: ‘|| To_char(S.Logon_time, ‘Dy Dd-Mon-Yy Hh24:Mi:Ss’) ||Chr(10)||
‘Last Call….: ‘|| To_char(Sysdate-(S.Last_call_et/60/60/24), ‘Dy Dd-Mon-Yy Hh24:Mi:Ss’) || ‘ -> ‘ || To_char(S.Last_call_et/60, ‘99999.0’) || ‘ Mins’ || To_char(S.Last_call_et/60/60, ‘99999.0’) || ‘ Hours’ ||To_char(S.Last_call_et/60/60/24, ‘99.0’) || ‘ Days’ ||Chr(10)||
‘Sql Address. : ‘||S.Sql_address ||Chr(10)||
‘Sql Hash…. : ‘||S.Sql_hash_value ||Chr(10)||
‘Prev Sql Hash: ‘||S.Prev_hash_value ||Chr(10)||
‘Trans Status : ‘|| Nvl(T.Status,’None’) || Chr(10)||
‘Trans Active : ‘|| Nvl(S.Taddr, ‘None’)||Chr(10)||
‘Undo Generation: ‘||Nvl(T.Used_ublk,0) || ‘ Blocks’||Chr(10)||
‘Changed Blocks : ‘||I.Block_changes||’ Blocks’||Chr(10)||
‘………… Current Sql Statment ……………..: ‘||Chr(10)||
‘=========================================================’||Chr(10)|| Nvl(Q.Sql_text,’No Current Sql Statment’) ||Chr(10)||
‘=========================================================’||Chr(10)||
‘……………. Prev Sql Statment ………………: ‘||Chr(10)||
‘=========================================================’||Chr(10)|| Nvl(Q2.Sql_text,’No Sql Statment’) ||Chr(10)||
‘=========================================================’
from gv$session s, gv$process p , gv$sqlarea q , gv$sqlarea q2 ,gv$session_wait w ,
gv$transaction t, gv$sess_io i
where p.addr=s.paddr
and s.sid=i.sid
and s.sid=nvl(‘&sid’,s.sid)
and s.sid=w.sid
and p.spid=nvl(‘&spid’,p.spid)
and q.HASH_VALUE(+)=s.sql_hash_value
and q2.hash_value(+)=s.prev_hash_value
and s.taddr=t.addr(+)
and nvl(s.process,-1) = nvl(‘&ClientPid’,nvl(s.process,-1));
