How to find an Oracle session from an OS PID

When top or ps shows a hot Oracle process, use its SPID to find the DB session. Query 1 is the RAC-aware SID lookup; query 2 extends it with SQL_ID and full SQL text so you can see exactly what the session is doing.

ACCEPT p_spid CHAR PROMPT 'OS PID (SPID): '

-- 1) Basic: SID / serial# / username from an OS PID (RAC-aware)
COL sid      FORMAT 99999
COL username FORMAT a20
COL osuser   FORMAT a15

SELECT p.spid, s.sid, s.serial#, s.username, s.osuser
FROM   gv$session s,
       gv$process p
WHERE  s.paddr = p.addr
AND    p.spid  = '&&p_spid'
ORDER BY p.spid;


-- 2) Extended: also return SQL_ID and full SQL text (single-instance)
COL pid FORMAT a10

SELECT s.osuser,
       p.spid                AS pid,
       s.username             AS dbuser,
       s.sid, s.serial#,
       s.status,
       s.last_call_et         AS active_time_sec,
       s.sql_id,
       sql.sql_fulltext
FROM   v$session s,
       v$process p,
       v$sql     sql
WHERE  s.paddr             = p.addr
AND    s.sql_id            = sql.sql_id
AND    s.sql_child_number  = sql.child_number
AND    p.spid              = '&p_spid';