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;

• ALTER SYSTEM DISCONNECT SESSION ‘sid,serial#’ POST_TRANSACTION
  ( waits for in-flight transactions to complete )
• ALTER SYSTEM DISCONNECT SESSION ‘sid,serial#’
  (I have read it works like ALTER SYSTEM KILL… IMMEDIATE? Yet to confirm by experiment)
• ALTER SYSTEM DISCONNECT SESSION ‘sid,serial#’ IMMEDIATE
   (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.