How to kill a session quick
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: How to kill a session quick

Hybrid View

  1. #1
    Join Date
    Mar 2001
    Posts
    286

    How to kill a session quick

    I used to ran into this problem:

    A user logs in and locks several tables. Even if I use "Aler system kill session ...", the session is only marked as "killed.". How do I make sure the session is killed quickly? Someone will suggest me to re-start the instance. But sometimes that's not an option.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Since you didn't specify, I'll assume you're on Solaris. Just kill the backend process.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Oct 2002
    Location
    CA
    Posts
    67
    If you are in Unix, try Unix kill -9.

    Good Luck
    --------------------------------------
    It's not what the world does to you that matters. It's how you respond...

  4. #4
    Join Date
    Mar 2001
    Posts
    286
    1. If on Unix, how do I know which process belong to which user. I need to be able to identify, say, the process of "SCOTT" and then kill -9 xxx.

    2. If this is on NT, does this work?

  5. #5
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Originally posted by dba_admin
    1. If on Unix, how do I know which process belong to which user. I need to be able to identify, say, the process of "SCOTT" and then kill -9 xxx.

    2. If this is on NT, does this work?
    1. Check SPID column in V$PROCESS to get Unix process ID.

    2. NT Task manager doesn't breakdown the information to thread level.

    Sanjay

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I am not sure if kill -9 is the best way, I have had pretty bad experience on tru64 killing the shadow process. If I kill from Oracle pmon goes slowly and freesup resources if I kill the shadow pmon process eats up 100% CPU

  7. #7
    Join Date
    Apr 2002
    Posts
    291
    If you are running on Unix, try to get PID of the required process, and then Use TOAD to kill that session, i.e. PID on Unix is same as SPID in TOAD. So, you can also find which process is running by which user through toad and then kill it from either TOAD or kill -9 .

    Thanks
    PNRDBA

  8. #8
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Still not found a sure-fire way to do this in NT.

    You can kill the locking sessions but they are only marked as being killed - and not actually killed. I had to reboot the instance.

    Anyone know a good method?

  9. #9
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405

    Re: How to kill a session quick

    Originally posted by dba_admin
    I used to ran into this problem:

    A user logs in and locks several tables. Even if I use "Aler system kill session ...", the session is only marked as "killed.". How do I make sure the session is killed quickly? Someone will suggest me to re-start the instance. But sometimes that's not an option.
    The session is killed immediately when you kill an active session. Marked as killed comes when you kill an inactice session. Marked for kill does not mean, the user is not killed. The user is killed, but Oracle marks the status as "killed" and the user details can be seen in v$session view. It does not mean, the user is still connected. The user is informed "session killed" when the user sub sequently queries the database and the user details are removed from v$session.

    It's like

    ACTIVE session killed:
    1. One active user is killed.
    2. User is disconnected from database and a message is displayed to user end terminal.
    3. User details are not available in v$session view.

    INACTIVE session killed:
    1. One inactive user is killed.
    2. User is disconnected from database.
    3. User is not informed about disconnection.
    4. User details are available in v$session view with status as "KILLED"
    5. User tries to query the database and is informed of killing.
    6. User details are rfemoved from v$session view.


    All these things are for a typical dedicated server database. Things may vary for MTS. And there is something to do with inband and outband connections. These connection types make a lot of difference when we kill the session.

    Can somebody refresh me with the details of these breaks???
    -nagarjuna

  10. #10
    Join Date
    Jun 2002
    Posts
    88
    That was a normal Rollback being done - U need to wait for it being done

    else in other cases U can try

    ALTER SYSTEM KILL &sid,&serial# IMMEDIATE;

    Thanks,
    Iyyappan.M

Posting Permissions

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



Click Here to Expand Forum to Full Width