Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2006
    Posts
    28

    Unanswered: sessions stay in "KILLED" state

    Hey All -

    I'm trying to kill a users process on my 10g server. I tried

    alter session kill session 'SID,SERIAL#' immediate

    This seems to put the session in "KILLED" status, where it remains for minutes,hours, days! According to the "BLOCKING_SESSION_STATUS" it's not blocked..? (IE: "NOT IN WAIT").

    I then tried:

    alter system disconnect session 'SID,SERIAL#' immediate

    but that doesn't help.

    I'm pretty sure the user was just doing a big select, so I don't think it's rolling back (how would I check if a process/session is rolling back a transaction?).

    So how can I get rid of this KILLED session? The only way I've found so far is to do a shutdown abort, which is a bit drastic!

    Thanks,

    Amy

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    For some OS Oracle won't actually make the session go away, unless or until the OS process is terminated.
    At least this has been my experience.
    HTH & YMMV
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    This is actually a feature according to Oracle (and no one else). The user has to either shutdown the connection at their side or try to access Oracle one more time before they get an error code indicating that they were killed. The oracle community has been complaining about this behavoir to oracle for years. There are a number of scripts that have been written that can be run via a cron job or a dbs_job that will scan for and kill the Oracle process being used by the connection. If you need an example, google using the following keywords "oracle kill sniped sessions"
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Jul 2006
    Posts
    28
    Thanks, both of you. That stinks...

    One more question: you suggested googling on "SNIPED" connections, but my connections appear as "KILLED", not "SNIPED"..?

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    In certain types of displays, the status shows as snipped. The problem is the same.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •