Re: Getting the Process Identifier (PID)

From: MarkP28665 <markp28665_at_aol.com>
Date: 27 Sep 1998 17:00:36 GMT
Message-ID: <19980927130036.06556.00001556@ng131.aol.com>

From: Michael Stickling >>
How can i get the UNIX PID from a oracle-user process? <<

The current or another session? For the current session use the userenv function to get the current sid and use that sid to join to the v$session table on the audit sid column and then join v$session to v$process.

For another session you can search v$session on any of numerious columns and then join to v$process. I have included two scripts that should provide you the SQL necessary to code what you want.

userenv example:
rem
rem file: stats_usersess
rem SQL*Plus script to display selected statistics to the user rem Written by Mark Powell
rem Note that the statistic number may change with an upgrade to Oracle rem so the stastics being collected are:

rem     37  db blocks gets
rem     38  consistant gets

rem 105 table scans (short tables)
rem 106 table scans (long tables)
rem 109 table scans rows gotten
rem 111 table fetchs by rowid
rem 119 sorts (memory)
rem 120 sorts (disk)
rem 121 sorts rows
rem

column Name        format a40
column Value       format a16
select sn.name "Name",
       to_char(nvl(st.value,0),'999,999,999,990')  "Value"
from   v$session se, v$sesstat st, v$statname sn
where  se.audsid      =  userenv('SESSIONID')
and    se.sid         =  st.sid

and st.statistic# in (37,38,105,106,109,111,119,120,121) and st.statistic# = sn.statistic#
order by sn.name

example v$session to v$process join:
set echo off
rem
rem filename: session_user.sql
rem SQL*Plus script to display selected session and related process infor- rem mation for a particular user.
rem
rem 11/27/95 s3527 m d powell new script rem
set verify off
column machine format a08 heading "APPL.|MACHINE" column sid format 99999
column serial# format 99999
column spid format 99999 heading "ORACLE|PROCESS" column process format 99999 heading "APPL.|PROCESS" column username format a12
REM

select s.username, s.status,  s.sid,     s.serial#,
       p.spid,     s.machine, s.process, s.lockwait

from v$session s, v$process p
where s.username = upper('&user_id')
and s.paddr = p.addr
/

You can easily change the &variable to be sid, Os level id, etc ...

Mark Powell -- Oracle 7 Certified DBA
- The only advice that counts is the advice that you follow so follow your own advice - Received on Sun Sep 27 1998 - 12:00:36 CDT