SQL to view OS process and Oracle session info

Use this commands and sql query to show information about OS process and Oracle user session.
Useful if for example you notice a single process is chewing up CPU on the server.

At the Unix prompt (replace 22910 with the actual process id):

$ ps -ef | grep 22910

oracle 22910     1 14 09:16:59 ?       32:09 oracleDBNAME
(DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))

In sqlplus or TOAD run this query:

SELECT	s.saddr, s.sid, s.serial#, s.username,
	s.osuser, s.machine, s.program, s.logon_time, s.status, 
	p.program, p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND p.spid IN (22910);

If otherwise you need to identify Unix process for a specific Oracle database session run SQL
statement below (assuming SIDs are 39 and 24, can also put username or osuser etc in whe where
clause):

SELECT	p.program, p.spid, s.saddr, s.sid, s.serial#, s.username,
	s.osuser, s.machine, s.program, s.logon_time, s.status 
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.sid IN (39, 24)

PROGRAM                        SPID
------------------------------ ---------   ...
oracle@hostname (TNS V1-V3)    590         ...
oracle@hostname (TNS V1-V3)    6190        ...

At the OS prompt get the process information

ps -ef | egrep '590|6190'

  oracle 18232 14573  0 10:16:17 pts/4    0:00 egrep 590|6190
  oracle  6190     1  0   Jul 28 ?       14:40 oracleDBNAME
(DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))
  oracle   590     1  0   Jul 30 ?       12:10 oracleDBNAME
(DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))