Orphan Processes in the database

When you need to find the OS-process on the database server for an oracle session (dedicated server) you can join v$session with v$process:

select p.spid  
from v$session s join v$process p on(s.paddr=p.addr)  
where s.sid=42;  

But if you kill a session with ‘alter system kill session …’  the link between these views are broken because the value in v$session.addr changes. In order to look for these orphan processes run this query:

select spid, program from v$process   
    where program!= 'PSEUDO'   
    and addr not in (select paddr from v$session)  
    and addr not in (select paddr from v$bgprocess)  
    and addr not in (select paddr from v$shared_server);  

You may check with OS tools like ps on Linux to see that these are indeed dead processes or with strace to see what they are doing and eventually kill them.