killing sessions and OS processes

been dealing with hung sessions today.
I'm still not sure of why are they being left there hanging. They are just ready to wait eternally for the "SQL*Net message from dblink" event. I guess that remote instance is failing or something.. I don't have access to that server, so I don't know what's happening there and admins are finns - pretty quiet types, they don't talk much. Anyway. I have faced a problem of having a big number of sessions either waiting for dblink or for client message and I KNOW that neither is coming. So a need in mass killing appears :) You can kill oracle sessions with

ALTER SYSTEM KILL SESSION SID, SERIAL# immediate;

But after you kill it like this, it still stays in the v$session view having the 'KILLED' status. And you cannot get rid of it otherwise but killing the process on the OS level. Because PMON waits for a client to issue next command so to send him "your session has been killed" message, before he can actually eliminate the process. Quite polite, but sometimes you just know that client is not coming back... For this matter there is a orakill.exe tool (for Windows). Used like this:

orakill [instance_name] [spid]

This one will kill the thread inside the oracle.exe process. You need to pass the spid of the process, taken from the v$process view.

So there were two problems in front of me:

1. To kill all the sessions started by a specific program.
2. To kill the processes so that all the resources and locks would be freed.

To solve the first one I coded a nice PL/SQL procedure:

CREATE OR REPLACE PROCEDURE KILL_SESSIONS_BY_PROGRAM (in_program IN varchar2) IS
CURSOR sessions_cur IS
select s.sid
,s.serial#
from v$session s
where lower(s.program)=lower(in_program)
and s.status != 'KILLED';
sessions_rec sessions_cur%ROWTYPE;
sess_to_kill varchar2(50);
cmd_string varchar2(100);
e_marked_for_kill exception;
PRAGMA EXCEPTION_INIT(e_marked_for_kill, -31);
begin
OPEN sessions_cur;
LOOP
FETCH sessions_cur INTO sessions_rec;
IF sessions_cur%ROWCOUNT = 0 AND sessions_cur%NOTFOUND THEN
RAISE_APPLICATION_ERROR (-20001,'NO sessions found!');
END IF;
EXIT WHEN sessions_cur%NOTFOUND;
sess_to_kill := TO_CHAR(sessions_rec.sid)','TO_CHAR(sessions_rec.serial#);
cmd_string:='ALTER SYSTEM KILL SESSION '''sess_to_kill''' immediate';
begin
DBMS_OUTPUT.PUT_LINE(cmd_string);
execute immediate cmd_string;
exception when e_marked_for_kill then
null;
end;
END LOOP;
end;
/

And for the second one I made a small SQLPlus script, which generates me a .cmd file containg as much orakill commands as it is needed. Here it is:

CLEAR BUFFER
set heading off;
set feedback off;
variable sess refcursor;
begin
open :sess for select 'orakill &instance ' p.spid
from v$session s, v$process p
where s.paddr = p.addr
and lower(s.program)=lower('&in_program')
and s.status = 'KILLED';
end;
/
spool h:\sql\kill.cmd;
print sess;
spool off;