RE: Finding orphan sessions
- From: "Khan, Muhammad S" <Muhammad.Khan@xxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 4 Jun 2007 10:48:18 -0400
Nice script Jared! Now the question is, how should these orphan
processes be dealt?
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jared Still
Sent: Friday, June 01, 2007 4:12 PM
To: tanmoydc@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Finding orphan sessions
On 5/22/07, Tanmoy <tanmoydc@xxxxxxxxx> wrote:
Hi,
We have a requirement where we need to find the Orphan session in
DB. Well, it happened some time back when one of our job get killed
after reaching its max time, but it's thread processes were running, so
we need a monitoring sql to check such process who does not have any
parent.
Possible ?
When joining v$session to v$process the v$process.spid column
should have a value (node process ID). If this is null, that is
generally
an orphaned session. Always has been in my experience anyway.
Jared
col username heading 'USERNAME' format a10
col sessions heading 'SESSIONS'
col sid heading 'SID' format 999
col status heading 'STATUS' format a10
col machine format a10 head 'MACHINE'
col client_program format a20 head 'CLIENT PROGRAM'
col server_program format a20 head 'SERVER PROGRAM'
col spid format a5 head 'SRVR|PID'
col serial# format 99999 head 'SERIAL#'
col client_process format 999999 head 'CLIENT|PID'
col osuser format a7
col logon_time format a17 head 'LOGON TIME'
col idle_time format a11 head 'IDLE TIME'
col ppid format 999 head 'PID'
set recsep off term on pause off verify off echo off
set line 200
set trimspool on
clear break
break on username skip 1
select
s.username,
s.sid,
s.serial#,
p.pid ppid,
s.status,
s.machine,
s.osuser,
substr(s.program,1,20) client_program,
s.process client_process,
substr(p.program,1,20) server_program,
p.spid spid,
to_char(logon_time, 'mm/dd/yy hh24:mi:ss') logon_time,
-- idle time
-- days added to hours
--( trunc(LAST_CALL_ET/86400) * 24 ) || ':' ||
-- days separately
substr('0'||trunc(LAST_CALL_ET/86400),-2,2) || ':' ||
-- hours
substr('0'||trunc(mod(LAST_CALL_ET,86400)/3600),-2,2) || ':' ||
-- minutes
substr('0'||trunc(mod(mod(LAST_CALL_ET,86400),3600)/60),-2,2) ||
':' ||
--seconds
substr('0'||mod(mod(mod(LAST_CALL_ET,86400),3600),60),-2,2)
idle_time
from v$session s, v$process p
where s.username is not null
-- use outer join to show sniped sessions in
-- v$session that don't have an OS process
and p.addr(+) = s.paddr
-- uncomment to see only your own session
--and userenv('SESSIONID') = s.audsid
order by username, sid
/
Other related posts:
