Thursday, July 8, 2010

Oracle sid and Linux pid

Sometimes we found Oracle process that has been running too long using top command on Linux. We know it has PID. But how to know the SID of the process?
Here is the script:

select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) box,
substr(b.username,1,10) username,
substr(b.osuser,1,8) os_user,
substr(b.program,1,30) program
from
v$session b,
v$process a
where
b.paddr = a.addr
and type='USER'
order by spid;