This discussion is archived
5 Replies Latest reply: Jan 11, 2013 11:28 AM by TSharma-Oracle RSS

ALTER SYSTEM KILL SESSION

983130 Newbie
Currently Being Moderated
Hello Everybody,

1) Is there any difference between “ALTER SYSTEM KILL SESSION & “kill -9”? Which one is the preferred method?

2) When we do alter system kill 'sid, serial#'. Are we killing the user process or the server process?

thanks in advance
  • 1. Re: ALTER SYSTEM KILL SESSION
    moreajays Pro
    Currently Being Moderated
    Hi,

    1) Is there any difference between “ALTER SYSTEM KILL SESSION & “kill -9”? Which one is the preferred method?
    Alter system will kill oracle shadow means user process , whereas kill -9 is an os command to kill server process from server itself , check by using ps -ef command
    2) When we do alter system kill 'sid, serial#'. Are we killing the user process or the server process?
    Killing user process
    Thanks,
    Ajay More
    http://moreajays.blogspot.com
  • 2. Re: ALTER SYSTEM KILL SESSION
    Fran Guru
    Currently Being Moderated
    0) welcome to OTN forums
    1) Preferible, Alter system kill session, if it doesn't work kill-9. "Kill -9" kill from the operating system side and Alter system kill from oracle side.
    2) you to kill an Oracle session, no process.
  • 3. Re: ALTER SYSTEM KILL SESSION
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    980127 wrote:
    Hello Everybody,

    1) Is there any difference between “ALTER SYSTEM KILL SESSION & “kill -9”? Which one is the preferred method?
    kill command is Os command that send a signal to a process or a group of processes.

    you could use man manual
    http://linux.die.net/man/3/kill

    For alter system kill session its oracle command ,and regarding to oracle document The KILL SESSION command doesn't actually kill the session. It merely asks the session to kill itself. In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete. In these cases the session will have a status of "marked for kill". It will then be killed as soon as possible.

    and you can use it in the following case :
    1-blocking lock occurs
    2-maximum connections reached
    3-when shutdown command hang
    2) When we do alter system kill 'sid, serial#'. Are we killing the user process or the server process?
    the command called alter system kill session ---> it's oracle command effect on oracle database.
  • 4. Re: ALTER SYSTEM KILL SESSION
    rp0428 Guru
    Currently Being Moderated
    Welcome to the forum!

    Whenever you post provide your 4 digit Oracle version (result of SELECT * FROM V$VERSION)
    >
    Hello Everybody,

    1) Is there any difference between “ALTER SYSTEM KILL SESSION & “kill -9”? Which one is the preferred method?

    2) When we do alter system kill 'sid, serial#'. Are we killing the user process or the server process?
    >
    You should only use the 'kill' from the OS as a last resort.

    There are TWO Oracle options: KILL session and DISCONNECT session. Only rarely, in my experience will DISCONNECT SESSION not get the job done.

    See ALTER SYSTEM in the SQL Language doc
    http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_2013.htm
    >
    end_session_clauses

    The end_session_clauses give you several ways to end the current session.

    DISCONNECT SESSION Clause
    Use the DISCONNECT SESSION clause to disconnect the current session by destroying the dedicated server process (or virtual circuit if the connection was made by way of a Shared Sever). To use this clause, your instance must have the database open. You must identify the session with both of the following values from the V$SESSION view:

    •For integer1, specify the value of the SID column.

    •For integer2, specify the value of the SERIAL# column.

    If system parameters are appropriately configured, then application failover will take effect.

    •The POST_TRANSACTION setting allows ongoing transactions to complete before the session is disconnected. If the session has no ongoing transactions, then this clause has the same effect described for as KILL SESSION.

    •The IMMEDIATE setting disconnects the session and recovers the entire session state immediately, without waiting for ongoing transactions to complete.

    ◦If you also specify POST_TRANSACTION and the session has ongoing transactions, then the IMMEDIATE keyword is ignored.

    ◦If you do not specify POST_TRANSACTION, or you specify POST_TRANSACTION but the session has no ongoing transactions, then this clause has the same effect as described for KILL SESSION IMMEDIATE.

    See Also:

    "Disconnecting a Session: Example"
    KILL SESSION Clause
    The KILL SESSION clause lets you mark a session as terminated, roll back ongoing transactions, release all session locks, and partially recover session resources. To use this clause, your instance must have the database open. Your session and the session to be terminated must be on the same instance unless you specify integer3.You must identify the session with the following values from the V$SESSION view:

    •For integer1, specify the value of the SID column.

    •For integer2, specify the value of the SERIAL# column.

    •For the optional integer3, specify the ID of the instance where the target session to be killed exists. You can find the instance ID by querying the GV$ tables.

    If the session is performing some activity that must be completed, such as waiting for a reply from a remote database or rolling back a transaction, then Oracle Database waits for this activity to complete, marks the session as terminated, and then returns control to you. If the waiting lasts a minute, then Oracle Database marks the session to be terminated and returns control to you with a message that the session is marked to be terminated. The PMON background process then marks the session as terminated when the activity is complete.

    Whether or not the session has an ongoing transaction, Oracle Database does not recover the entire session state until the session user issues a request to the session and receives a message that the session has been terminated.

    See Also:

    "Terminating a Session: Example"
    IMMEDIATE Specify IMMEDIATE to instruct Oracle Database to roll back ongoing transactions, release all session locks, recover the entire session state, and return control to you immediately.
  • 5. Re: ALTER SYSTEM KILL SESSION
    TSharma-Oracle Guru
    Currently Being Moderated
    1) Is there any difference between “ALTER SYSTEM KILL SESSION & “kill -9”? Which one is the preferred method?

    Answer 1. "Alter system kill session" kills the user process & "kill -9" kills the server level process. Always kills the session with "Alter system kill session" but sometimes if it stucks or taking very long time than go ahead and kill server process using "kill -9".

    2) When we do alter system kill 'sid, serial#'. Are we killing the user process or the server process?

    Answer: User process

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points