Re: map SQLNET connects to process ids on UNIX

From: Peter Moore <ptmoore_at_sequent.com>
Date: Wed, 08 Jul 1998 08:24:12 GMT
Message-ID: <35a32c43.3446192@news.sequent.com>

I use the following script to give a nice view of the non-background processes, their O/S usernames, O/S process IDs, and Oracle usernames and process IDs.

col osuser form a20
col "sess user" form a10
col "proc user" form a10
col sid form 999999

col "sess process" form 9999999999
col "machine" form a12
col "program" form a30

set echo off verify off lines 140 pages 45

select s.osuser "OSUser",
       s.username "Sess User",
       s.sid "SID",
       s.process "Sess Process",
       s.machine "Machine",
       p.pid "PID",
       p.spid "SPID",
       p.username "Proc User",
       p.program "Program"

from v$session s, v$process p
where p.addr = s.paddr

and   (s.username like '%'||UPPER('&&user')||'%'
  or   p.username like '%'||UPPER('&&user')||'%')
and   s.osuser like '%'||lower('&&osuser')||'%'
and   s.sid like NVL('&&oracle_sid','%')
and   p.pid like NVL('&&oracle_pid','%')

and p.spid like NVL('&&unix_pid','%') and p.program like DECODE(NVL(UPPER('&&Net_V2_YN'),'%'),

                            'Y','%TNS%',
                            'N','%Two-Task%',
                            '%')

order by s.osuser
/

Scott Cote <scottcote_at_contractor.net> wrote:

> Link the addr field in v$process to the paddr field of v$session. If you are not
> MTS, then this will give you the pid of the client (v$session.process) and the pid
> of the server process (v$process.spid) .
>
> Kevin Brand wrote:
>
> > How can I determine which process ( oracleSID ) is associated with a particular
> > user that is coming in over SQLNET? The PROCESS field does not indicate the
> > correct pid, or the correct format for a pid.
--
Peter Moore
DBA, IS Ops, Sequent Computer Systems, Weybridge, UK Email: ptmoore_at_sequent.com Phone: +44 1932 814737 Received on Wed Jul 08 1998 - 03:24:12 CDT