1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How To Kill Oracle Database Session

Discussion in 'General' started by prasuna, Nov 19, 2012.

  1. prasuna

    prasuna Active Member

    Messages:
    4
    Likes Received:
    1
    Trophy Points:
    85
    How To Kill Oracle Database Session

    First identify the offending sessions using active running processes query :

    The SQL*Plus Approach
    -------------------------------
    Sessions can be killed from within oracle using the ALTER SYSTEM KILL SESSION syntax.

    SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

    In some situations the Oracle.exe is not able to kill the session immediately.
    In these cases the session will be "marked for kill". It will then be killed as soon as possible.

    Issuing the ALTER SYSTEM KILL SESSION command is the only safe way to kill an Oracle session.
    If the marked session persists for some time you may consider killing the process at the
    Operating system level, as explained below.
    Killing OS processes is dangerous and can lead to instance failures, so do this at your own peril.

    It is possible to force the kill by adding the IMMEDIATE keyword:

    SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
    This should prevent you ever needing to use the orakill.exe in Windows, or the kill command in UNIX/Linux.

    The NT Approach
    ----------------------
    To kill the session via the NT operating system

    The SID and SPID values of the relevant session can then be substituted into the following command issued from the command line:

    C:> orakill ORACLE_SID spid
    The session thread should be killed immediately and all resources released.

    The UNIX Approach
    ---------------------
    To kill the session via the UNIX operating system
    then substitute the relevant SPID into the following command:

    % kill -9 spid
    If in doubt check that the SPID matches the UNIX PROCESSID shown using:

    % ps -ef | grep ora
    The session thread should be killed immediately and all resources released

    note:-sid is different from ORACLE_SID
     
  2. mn185015

    mn185015 Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Noida
    your article is not complete, you havn't included how to find out the sid and serial of a process which you want to kill.

    Thank YOu.
     
  3. soumya.parhi

    soumya.parhi Premium Member

    Messages:
    33
    Likes Received:
    2
    Trophy Points:
    185
    Location:
    India
    A.Check if the Package or table are locked using the below query
    SELECT b.object_name,
    a.session_id,
    a.oracle_username,
    a.os_user_name,
    a.process,
    a.locked_mode
    FROM v$locked_object a,
    all_objects b
    WHERE a.object_id = b.object_id
    B.Get the serial number for the session based on the session id got from above qyery.
    SELECT SID,
    serial#,
    ownerid,
    status,
    server,
    username,
    osuser,
    process,
    machine
    FROM v$session
    WHERE SID = 'Session id from above query'
    C. Command to kill the session ALTER SYSTEM KILL SESSION 'Sid from query, Serial# from Query 2'


    Thanks,
    Soumya
     
  4. mustaq

    mustaq Guest

    Generalized syntax will be
    ALTER SYSTEM KILL SESSION '<SID, SERIAL#>'
     
  5. afewgman

    afewgman Guest

    thanks for good info.:)
     
  6. shreya92

    shreya92 Active Member

    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    80
    you can consult Rolta, they provide oracle applications, so you could get the answer for your query by consulting them personally.
    Check out : rolta.com/services/oracle-applications/ might help you out.
     
  7. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula