kill oracle session相关描述

近期看到不少朋友都对kill session相关的参数不太熟悉,下面是摘自Oracle® Database SQL Reference

语法参考

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.
o	If you also specify POST_TRANSACTION and the session has ongoing transactions, then the IMMEDIATE keyword is ignored.
o	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.

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.

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.

执行案例

--Oracle 级别kill
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

--Linux/Unix kill
kill -9 spid

--Windows kill
orakill ORACLE_SID spid