Neil Chandler's DBA Blog

A resource for Database Professionals

Killing a session. Dead!

with one comment

… 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.

Written by Neil Chandler

25th July 2013 at 18:59

One Response

Subscribe to comments with RSS.

  1. 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: http://tinyurl.com/oozyud2

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

    Regards,
    Marko

    Marko Sutic

    26th July 2013 at 07:19


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 27 other followers

%d bloggers like this: