6

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.

  • Are you running RAC? Linux or Windows as the DB box? – Philᵀᴹ Aug 4 '12 at 0:35
  • 2
    I may as well also mention that if developers are leaving locks on a production system that cause things to fail - well, you need to fix the access they have and create a pre-production environment. – Philᵀᴹ Aug 4 '12 at 0:44
  • No RAC, Linux OS. This is a pre-prod environment, and educating the users is out of question. :) – Florin Andrei Aug 6 '12 at 19:55
  • "The session is still there" meaning stuff is still locked? Sessions might 'hang around' but the locks should not. – Jack Douglas Oct 3 '12 at 17:33
3

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.

1

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

  • KILL doesn't work either. – Florin Andrei Aug 6 '12 at 19:56
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.