RE: Finding orphan sessions

From: Khan, Muhammad S <Muhammad.Khan_at_ca.com>
Date: Mon, 4 Jun 2007 10:48:18 -0400
Message-ID: <06FBECFB25A476489623850EE472C29F03759E4A@USILMS12.ca.com>

 

Nice script Jared! Now the question is, how should these orphan processes be dealt?

---

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jared Still Sent: Friday, June 01, 2007 4:12 PM
To: tanmoydc_at_gmail.com
Cc: oracle-l_at_freelists.org
Subject: Re: Finding orphan sessions    

On 5/22/07, Tanmoy <tanmoydc_at_gmail.com> 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 /

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jun 04 2007 - 09:48:18 CDT