Re: Job queue processes

From: Jared Still <jkstill_at_gmail.com>
Date: Wed, 8 Nov 2006 10:50:28 -0800
Message-ID: <bf46380611081050kf63519cx21b6379956ebf405@mail.gmail.com>

On 11/1/06, rjamya <rjamya_at_gmail.com> wrote:
>
> Is there a way to link a j00x process to a job that is actively
> running? 9204/10.x ?
>
> I looked in the dictionary but couldn't find anything.
>
>

This should work. Tested on 9i, but not on 10g. The trick is just to filter on the server_program for '%(J%'

col username heading 'USERNAME' format a10 col sessions heading 'SESSIONS'
col sid heading 'SID' format 999
col status heading 'STATUS' format a10
col server_program format a20 head 'SERVER PROGRAM' col spid format a5 head 'SRVR|PID'
col serial# format 99999 head 'SERIAL#'
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,
        p.program 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 and p.program like '%(J%' order by username, sid /

set recsep wrapped

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

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

Received on Wed Nov 08 2006 - 12:50:28 CST