SELECT s.sid, s.serial#, s.username, s.osuser, p.spid "Linux process id", s.machine, p.terminal, s.program
FROM v$session s, v$process p
WHERE s.paddr = p.addr;


-- Get Linux processes that have no related session(s)
select inst_id, spid, program from gv$process where addr not in ( select paddr from gv$session ) and spid is not null


-- Generate Linux kill commands for processes that have no sessions (sessions that may have been killed at Oracle level
select p.inst_id,s.sid, s.serial#, s.program, s.username, p.spid, p.username,
       'kill -9 '||p.spid
from 
gv$session s
RIGHT OUTER JOIN
gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
where p.spid in(
SELECT spid
FROM   gv$process
WHERE NOT EXISTS (SELECT 1
                  FROM gv$session
                  WHERE paddr = addr));