Killing a session. Dead!

… or what ALTER SYSTEM KILL SESSION doesn’t do.

So, you have a runaway session and you want it to stop now, please. You enter “ALTER SYSTEM KILL SESSION (sid,serial#) IMMEDIATE” – supplying the parameters from GV$SESSION (adding @instance_id if it’s running in a different node) and you wait a minute…. and eventually it gets “ORA-00031: session marked for kill” [“KILLED”]. So why didn’t it die immediately? What’s going on? Isn’t KILL a strong enough command?

What actually happened when you submit “ALTER SYSTEM KILL SESSION” is that a flag within the session it set to for the session to kill itself. It’s not so much KILL, as please commit suicide when you notice that I’ve asked. The session needs to wait for a chance to notice the flag, die and clean-up the session.

While you’re waiting for this to happen, it’s not uncommon for DBA’s to hop onto the operating system and do a “kill -9” (or orakill on Windows) to wipe out the process/thread that was running. This then causes SMON to clean up the mess, rather than the user process (which may or may not be desirable – SMON can spawn parallel clean-up slaves which can speed things up).

So, what if you have no access to the O/S, or want neater, tidier (and safer?) results? Use the ALTER SYSTEM DISCONNECT SESSION command instead. Get the same SID and SERIAL# from (g)v$session and give it a go. You have 3 options;

    ( waits for in-flight transactions to complete )
    (I have read it works like ALTER SYSTEM KILL… IMMEDIATE? Yet to confirm by experiment)
     (just like kill -9; wipes the O/S process out.)

So, the next time you want that session real dead, real quick, you know what to do.


2 Responses to Killing a session. Dead!

  1. Marko Sutic says:

    Hello Neil,

    sometimes you just need to execute “kill -9” on the OS side.
    I’ve experienced situation when session was flagged as “marked for kill” forever whatever I do from database layer.

    I had to create job that will kill stuck process from the OS side.
    Blog post:

    It would be much easier and prettier just to execute “ALTER SYSTEM DISCONNECT SESSION ‘sid,serial#’ IMMEDIATE” 🙂



  2. Rohit Kishore says:



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.