Sunday, April 6, 2008

Kill hung Session

Sometimes we need to kill hung session. After being killed, usually the session will be disappeared as soon as possible. But we are confused, why the killed session still appear in v$session with status "KILLED" ?

If killing via SQLPLUS do not release the session, then we can kill the hung session via operating system.

Get the operating system ID (SPID).

For example, we will kill the SPID of SID=37

SELECT s.sid,s.serial#,p.spid,s.osuser,s.status
FROM v$process p, v$session s
WHERE p.addr = s.paddr and SID=37;

Kill the related SPID.

For example, above query get SPID=5768
In Unix
     $ kill -9 5768
In windows, mention the intance_name. For example intance_name=DWHPROD
     C:> orakill DWHPROD 5768