11g之前：

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);

11g之后：

select * from v$process where addr=(select creator_addr from v$session where sid=140);

参考：
How To Find The Process Identifier (pid, spid) After The Corresponding Session Is Killed? (Doc ID 387077.1)