Re: Finding orphan sessions

From: Jared Still <jkstill_at_gmail.com>
Date: Fri, 1 Jun 2007 13:11:42 -0700
Message-ID: <bf46380706011311g3d700386tdbbf9bbe819df9d6@mail.gmail.com>

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 Fri Jun 01 2007 - 15:11:42 CDT