Is there a way to trace a unix process id to a SID and SERIAL# ?

The Oracle (tm) Users' Co-Operative FAQ

Is there a way to trace a unix process id to a SID and SERIAL# ?

Yes the client UNIX pid can be used to locate the Oracle session id and background process, and the database server user process pid can be used to find the client front-end process, at least in a dedicated server environment.

Basically execute the following sql:

	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.process  = '&unix_pid'
	and    s.paddr    = p.addr
	/

Be sure that you are using the UNIX pid for the process that actually connected to Oracle in the query. You can use the UNIX ps command to trace the parent/child process chain to find the last child in the chain by greping on the pid of interest. In this example 6010 is the pid you would want to use.

ps –ef | grep user01
user01 24913 24724   0  08:37:54 ttyAE/AbE+  0:00 mrpapp / -t LWD:PC -f lwp:vt220 
user01  6009 24913   0  10:32:18 ttyAE/AbE+  0:00 sh -c  f60run /jit/bin/eds30/r51ic/bin/icrp.fmx $PASSWORD lwp:vt220    
user01  6010  6009   0  10:32:18 ttyAE/AbE+  0:00 f60run /jit/bin/eds30/r51ic/bin/icrp.fmx / lwp:vt220

For finding the front-end for the Oracle user session background process modify the query above (line 4) where clause to read:

	where  p.spid     = &oracle_pid

You will want to modify the select column list as desired. And I have never tested these queries in an MTS environment, but these queries have proved very useful in a dedicated server environment. You may wish to see the FAQ entry Who is locking a specific row in a table? for related information.

Further reading: See the Reference manual, Oracle 8i Reference, for the list of and information on the v$ dynamic performance views.