[20180918]disconnect session和kill session的區別.txt

[20180918]disconnect session和kill session的區別.txt

http://fatihacar.com/blog/show-and-kill-transaction-lock-in-oracle/

Show and Kill Transaction Lock in Oracle
Category: Administration — Fatih Acar @ 23:19

You can see the transaction lock with enterprise manager or command prompt.

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.

The ALTER SYSTEM DISCONNECT SESSION syntax as an alternative method for killing Oracle sessions. Unlike the KILL SESSION
command which asks the session to kill itself, the DISCONNECT SESSION command kills the dedicated server process (or
virtual circuit when using Shared Sever), which is equivalent to killing the server process from the operating system.
The basic syntax is similar to the KILL SESSION command with the addition of the POST_TRANSACTION clause. The SID and
SERIAL# values of the relevant session can be substituted into one of the following statements.

The POST_TRANSACTION clause waits for ongoing transactions to complete before disconnecting the session, while the
IMMEDIATE clause disconnects the session and ongoing transactions are recovered immediately.

With Command Prompt

SQL> select SID,serial#,username from v$session where SID in (select blocking_session from v$session);
If you want to kill session, you can use below command.

SQL>alter system kill session 'SID,SERIAL#' immediate;
or
SQL>alter system disconnect session 'SID,SERIAL#' immediate;
SQL>alter system disconnect sessiob 'SID,SERIAL#' post_transaction;

--//視乎文件講alter system disconnect session 才可能是kill session.
--//oracle 命令的命令方式真不科學,個人感覺!!
--//不過作者提到
alter system kill session 'SID,SERIAL#' immediate;

--//加入immediate效果應該一樣的.
--//再轉載一個連結:

https://www.cnblogs.com/andy6/p/5778363.html
如何徹底殺掉不良使用者會話

在ORACLE資料庫當中，有時候會使用ALTER SYSTEM KILL SESSION 'sid,serial#'殺掉一個會話程式，但是使用這個SQL語句殺掉會話後，
資料庫並不會立即釋放掉相關的資源，有時候你會發現鎖定的資源很長時間也不會釋放，即使會話狀態為"KILLED"，依然會阻塞其它會
話。

在ORACLE資料庫殺掉會話程式有三種方式：

1： ALTER SYSTEM KILL SESSION

關於KILL SESSION Clause ，如下官方文件描述所示，alter system kill session實際上不是真正的殺死會話，它只是將會話標記為終
止。等待PMON程式來清除會話。

select sid,serial# from v$session where username='DEMO';查詢使用者的 sid,serial#

可以使用ALTER SYSTEM KILL SESSION 'sid,serial#' 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.

2： ALTER SYSTEM DISCONNECT SESSION

ALTER SYSTEM DISCONNECT SESSION 殺掉專用伺服器(DEDICATED SERVER)或共享伺服器的連線會話，它等價於從作業系統殺掉程式。它有
兩個選項POST_TRANSACTION和IMMEDIATE， 其中POST_TRANSACTION表示等待事務完成後斷開會話，IMMEDIATE表示中斷會話，立即回滾事
務。

SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

3： KILL -9 SPID (Linux) 或 orakill ORACLE_SID spid　(Windows)

可以使用下面SQL語句找到對應的作業系統程式SPID，然後殺掉。當然殺掉作業系統程式是一件危險的事情，尤其不要誤殺。所以在執行
前，一定要謹慎確認。

select SPID from v$process where addr in (SELECT PADDR FROM V$SESSION WHERE USERNAME='使用者名稱字');      (找到使用者的spid)

kill -9 spid

在資料庫如果要徹底殺掉一個會話，尤其是大事務會話，最好是使用ALTER SYSTEM DISCONNECT SESSION IMMEDIATE或使用下面步驟：

1：首先在作業系統級別Kill掉程式。

2：在資料庫內部KILL SESSION

或者反過來亦可。這樣可以快速終止程式，釋放資源。

--//我的測試:
1.環境:
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

2.測試alter system kill session 'sid,serial#' immediate;

--//session 1:
SCOTT@test01p> @ spid
       SID    SERIAL# PROCESS                  SERVER    SPID                     PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------
       151          5 5356:2724                DEDICATED 2892                      22          3 alter system kill session '151,5' immediate;

--//session 2:
SYS@test> alter system kill session '151,5' immediate;
System altered.

--//session 1:
SCOTT@test01p> select sysdete from dual ;
select sysdete from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 2892
Session ID: 151 Serial number: 5
ERROR:
ORA-03114: not connected to ORACLE

--//如果rac環境應該寫成:
alter system kill session 'sid,serial#,@inst_id' immediate;

3.測試 ALTER SYSTEM DISCONNECT SESSION:
--//session 1:
SCOTT@test01p> @ spid

       SID    SERIAL# PROCESS                  SERVER    SPID                     PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------
        93        523 1920:3748                DEDICATED 5632                      57         22 alter system kill session '93,523' immediate;

--//session 2:        
SYS@test> alter system DISCONNECT session '93,523' immediate;
System altered.


--//session 1:
SCOTT@test01p> select sysdete from dual ;
select sysdete from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 5632
Session ID: 93 Serial number: 523