ALTER SYSTEM DISCONNECT SESSION doesn't work

Question

Oracle 11g. The devs leave sessions connected overnight, sometimes certain cron jobs fail because of that. I am trying to kill all user sessions, but I can't.

I login as:

sqlplus system/[password] as sysdba

I do this to list the sessions:

select sid,serial#,program from gv$session where type='USER' and machine!='this-server';

And the list looks like this:

       SID    SERIAL# PROGRAM

---

10   7883 toad.exe
11  42365 toad.exe
77  22493 toad.exe

Then I do:

alter system disconnect session '10,7883' immediate;

And is says:

System altered.

But nothing happens. The session is still there. What am I doing wrong?

P.S.: I'm pretty new to Oracle, I only used MySQL before.

Answer 1

If even an ALTER SYSTEM KILL... does not remove the session after a bit, then the only thing you are left with is to kill it on the OS level. In Linux you can get the command to kill the process as follows:

SELECT 'kill ' || ' -9 ' || p.spid 
FROM v$session s
JOIN v$process p ON s.paddr = p.addr 
WHERE s.sid=:SID_To_Kill;

In Windows you can get the command to kill the thread as follows:

SELECT 'orakill ' || sys_context('USERENV', 'DB_NAME') || ' ' || p.spid 
FROM v$session s 
JOIN v$process p ON s.paddr = p.addr 
WHERE s.sid=:SID_To_Kill;

It might be simpler just to have cron bounce the database before running the jobs.

Answer 2

You should probably be using "Alter system KILL session 'sid,serial';" Disconnect should be a clean disconnect, but sounds like you need the kill.

Answer 3

We've seen zombie connections survive after the client PC is rebooted, even powered off. Oracle DB is 11G, running on RHEL5. If the session was holding a lock, the lock stays in place until the database is rebooted or the Unix thread is killed. It seems to be a bug in Ora 11G.