Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Are you running RAC? Linux or Windows as the DB box? –  FreshPhilOfSO 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. –  FreshPhilOfSO 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
add comment

3 Answers

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.

share|improve this answer
add comment

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

share|improve this answer
    
KILL doesn't work either. –  Florin Andrei Aug 6 '12 at 19:56
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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