Missing OS process id and Database Session Status is Killed

On Unix-based systems, killing the session using database commands i.e. ALTER SYSTEM KILL/DISCONNECT will sometimes cause the session to turn into a zombie/ghost (with status KILLED on v$session), even if that statement is not doing any DMLs (just select). So why is the session taking time to rollback if there were no data changes?

We usually fix these zombie/ghost sessions by killing the actual OS process id. To find the os process id we use the query below (match v$session and v$process).

SELECT sess.sid,
FROM v$session sess, v$process proc
WHERE sess.paddr = proc.addr
and sess.sid = '&session_id';

However, there are cases when killing the session, the corresponding information on v$process is lost(deleted/gone). To find the os process id, I use the query below to match OS process IDs on v$process that does not have a corresponding info on v$session. Making sure to use the ‘like %TNS% filter’ to avoid killing database critical os processes.

select 'kill -9 ' || spid
from v$process
where addr not in (select paddr from v$session) and program like '%TNS%';

If you need more help with database issues, feel free to send me an email (jimmycdo@yahoo.com) and I'll gladly help.

About Jimbart