ORACLE快速徹底Kill掉的會話
http://www.cnblogs.com/kerrycode/p/4034231.html
在ORACLE資料庫當中，有時候會使用ALTER SYSTEM KILL SESSION 'sid,serial#'殺掉一個會話程式，但是使用這個SQL語句殺掉會話後，資料庫並不會立即釋放掉相關的資源，有時候你會發現鎖定的資源很長時間也不會釋放，即使會話狀態為“KILLED”，依然會阻塞其它會話。
下面根據Eygel的"Oracle中Kill session的研究"，構造一個案例看看kill session到底做了什麼。如下所示
會話1：
SQL> conn etl/etl
Connected.
SQL> update test set status='invalid';
55944 rows updated.
SQL> update test2 set dropped='Y';
3090 rows updated.
會話2：
SQL> show user
USER is "SYS"
SQL> update etl.test2 set dropped='Y';
3090 rows updated.
會話3
SQL> select saddr,sid,serial#,paddr,username,status from v$session where username =upper('etl') or username =upper('sys');
SADDR SID SERIAL# PADDR USERNAME STATUS
---------------- ---------- ---------- ---------------- ------------------------------ --------
000000025C233B00 27 33353 000000025F1D1FC8 ETL INACTIVE
000000025C23A608 37 11448 000000025F1D27B0 SYS ACTIVE
000000025C24BC50 63 54311 000000025F1D5F08 SYS ACTIVE
SQL> alter system kill session '27,33353';
System altered.
SQL> select saddr,sid,serial#,paddr,username,status from v$session where username =upper('etl') or username =upper('sys');
SADDR SID SERIAL# PADDR USERNAME STATUS
---------------- ---------- ---------- ---------------- ------------------------------ --------
000000025C233B00 27 33353 000000025C21A0B0 ETL KILLED
000000025C23A608 37 11448 000000025F1D27B0 SYS ACTIVE
000000025C24BC50 63 54311 000000025F1D5F08 SYS INACTIVE
如下所示，我殺掉了其中兩個會話後，這兩個會話的地址都變為000000025C21A0B0了（請見PADDR列）。當在Oracle中kill session以後, Oracle只是簡單的把相關session的paddr 指向同一個虛擬地址.此時v$process和v$session失去關聯,程式就此中斷。 然後Oracle就等待PMON去清除這些Session.所以通常等待一個被標記為Killed的Session退出需要花費很長的時間. 如果此時被Kill的process，重新嘗試執行任務，那麼馬上會收到程式中斷的提示，process退出，此時Oracle會立即啟動PMON 來清除該session.這被作為一次異常中斷處理.
SQL> alter system kill session '63,54311';
System altered.
SQL> select saddr,sid,serial#,paddr,username,status
from v$session where username =upper('etl') or username =upper('sys');
SADDR SID SERIAL# PADDR USERNAME STATUS
---------------- ---------- ---------- ---------------- ------------------------------ --------
000000025C233B00 27 33353 000000025C21A0B0 ETL KILLED
000000025C23A608 37 11448 000000025F1D27B0 SYS ACTIVE
000000025C24BC50 63 54311 000000025C21A0B0 SYS KILLED
我們根據下面SQL找到程式的地址，然後在v$process裡面找到對應的spid,然後從作業系統中殺掉該程式。
SQL> select p.addr from v$process p where pid <> 1
2 minus
3 select s.paddr from v$session s;
ADDR
----------------
000000025F1D1FC8
000000025F1D5F08
SQL> select saddr,sid,serial#,paddr,username,status from v$session
where username =upper('etl') or username =upper('sys');
SADDR SID SERIAL# PADDR USERNAME STATUS
---------------- ---------- ---------- ---------------- ------------------------------ --------
000000025C233B00 27 33353 000000025C21A0B0 ETL KILLED
000000025C23A608 37 11448 000000025F1D27B0 SYS ACTIVE
000000025C24BC50 63 54311 000000025C21A0B0 SYS KILLED
SQL> select addr, pid, spid, username from v$process where addr in ('000000025F1D1FC8','000000025F1D5F08');
ADDR PID SPID USERNAME
---------------- ---------- ------------ ---------------
000000025F1D1FC8 22 12959 oracle
000000025F1D5F08 38 12971 oracle
SQL> ! kill -9 12959
SQL> ! kill -9 12971
SQL> select saddr,sid,serial#,paddr,username,status
from v$session where username =upper('etl') or username =upper('sys');
SADDR SID SERIAL# PADDR USERNAME STATUS
---------------- ---------- ---------- ---------------- ------------------------------ --------
000000025C23A608 37 11448 000000025F1D27B0 SYS ACTIVE
在ORACLE資料庫殺掉會話程式有三種方式：
1： ALTER SYSTEM KILL SESSION
關於KILL SESSION Clause ，如下官方文件描述所示，alter system kill session實際上不是真正的殺死會話，它只是將會話標記為終止。等待PMON程式來清除會話。
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.
可以使用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，然後殺掉。當然殺掉作業系統程式是一件危險的事情，尤其不要誤殺。所以在執行前，一定要謹慎確認。
SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';
在資料庫如果要徹底殺掉一個會話，尤其是大事務會話，最好是使用ALTER SYSTEM DISCONNECT SESSION IMMEDIATE或使用下面步驟：
1：首先在作業系統級別Kill掉程式。
2：在資料庫內部KILL SESSION
或者反過來亦可。這樣可以快速終止程式，釋放資源。
參考資料：
http://www.eygle.com/faq/Kill_Session.htm
http://www.oracle-base.com/articles/misc/killing-oracle-sessions.php#unix
http://www.eygle.com/archives/2005/10/oracle_howto_kill_session.html
http://blog.sina.com.cn/s/blog_6d6e54f70100zfqp.html
http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_2013.htm#SQLRF53047
在ORACLE資料庫當中，有時候會使用ALTER SYSTEM KILL SESSION 'sid,serial#'殺掉一個會話程式，但是使用這個SQL語句殺掉會話後，資料庫並不會立即釋放掉相關的資源，有時候你會發現鎖定的資源很長時間也不會釋放，即使會話狀態為“KILLED”，依然會阻塞其它會話。
下面根據Eygel的"Oracle中Kill session的研究"，構造一個案例看看kill session到底做了什麼。如下所示
會話1：
SQL> conn etl/etl
Connected.
SQL> update test set status='invalid';
55944 rows updated.
SQL> update test2 set dropped='Y';
3090 rows updated.
會話2：
SQL> show user
USER is "SYS"
SQL> update etl.test2 set dropped='Y';
3090 rows updated.
會話3
SQL> select saddr,sid,serial#,paddr,username,status from v$session where username =upper('etl') or username =upper('sys');
SADDR SID SERIAL# PADDR USERNAME STATUS
---------------- ---------- ---------- ---------------- ------------------------------ --------
000000025C233B00 27 33353 000000025F1D1FC8 ETL INACTIVE
000000025C23A608 37 11448 000000025F1D27B0 SYS ACTIVE
000000025C24BC50 63 54311 000000025F1D5F08 SYS ACTIVE
SQL> alter system kill session '27,33353';
System altered.
SQL> select saddr,sid,serial#,paddr,username,status from v$session where username =upper('etl') or username =upper('sys');
SADDR SID SERIAL# PADDR USERNAME STATUS
---------------- ---------- ---------- ---------------- ------------------------------ --------
000000025C233B00 27 33353 000000025C21A0B0 ETL KILLED
000000025C23A608 37 11448 000000025F1D27B0 SYS ACTIVE
000000025C24BC50 63 54311 000000025F1D5F08 SYS INACTIVE
如下所示，我殺掉了其中兩個會話後，這兩個會話的地址都變為000000025C21A0B0了（請見PADDR列）。當在Oracle中kill session以後, Oracle只是簡單的把相關session的paddr 指向同一個虛擬地址.此時v$process和v$session失去關聯,程式就此中斷。 然後Oracle就等待PMON去清除這些Session.所以通常等待一個被標記為Killed的Session退出需要花費很長的時間. 如果此時被Kill的process，重新嘗試執行任務，那麼馬上會收到程式中斷的提示，process退出，此時Oracle會立即啟動PMON 來清除該session.這被作為一次異常中斷處理.
SQL> alter system kill session '63,54311';
System altered.
SQL> select saddr,sid,serial#,paddr,username,status
from v$session where username =upper('etl') or username =upper('sys');
SADDR SID SERIAL# PADDR USERNAME STATUS
---------------- ---------- ---------- ---------------- ------------------------------ --------
000000025C233B00 27 33353 000000025C21A0B0 ETL KILLED
000000025C23A608 37 11448 000000025F1D27B0 SYS ACTIVE
000000025C24BC50 63 54311 000000025C21A0B0 SYS KILLED
我們根據下面SQL找到程式的地址，然後在v$process裡面找到對應的spid,然後從作業系統中殺掉該程式。
SQL> select p.addr from v$process p where pid <> 1
2 minus
3 select s.paddr from v$session s;
ADDR
----------------
000000025F1D1FC8
000000025F1D5F08
SQL> select saddr,sid,serial#,paddr,username,status from v$session
where username =upper('etl') or username =upper('sys');
SADDR SID SERIAL# PADDR USERNAME STATUS
---------------- ---------- ---------- ---------------- ------------------------------ --------
000000025C233B00 27 33353 000000025C21A0B0 ETL KILLED
000000025C23A608 37 11448 000000025F1D27B0 SYS ACTIVE
000000025C24BC50 63 54311 000000025C21A0B0 SYS KILLED
SQL> select addr, pid, spid, username from v$process where addr in ('000000025F1D1FC8','000000025F1D5F08');
ADDR PID SPID USERNAME
---------------- ---------- ------------ ---------------
000000025F1D1FC8 22 12959 oracle
000000025F1D5F08 38 12971 oracle
SQL> ! kill -9 12959
SQL> ! kill -9 12971
SQL> select saddr,sid,serial#,paddr,username,status
from v$session where username =upper('etl') or username =upper('sys');
SADDR SID SERIAL# PADDR USERNAME STATUS
---------------- ---------- ---------- ---------------- ------------------------------ --------
000000025C23A608 37 11448 000000025F1D27B0 SYS ACTIVE
在ORACLE資料庫殺掉會話程式有三種方式：
1： ALTER SYSTEM KILL SESSION
關於KILL SESSION Clause ，如下官方文件描述所示，alter system kill session實際上不是真正的殺死會話，它只是將會話標記為終止。等待PMON程式來清除會話。
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.
可以使用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，然後殺掉。當然殺掉作業系統程式是一件危險的事情，尤其不要誤殺。所以在執行前，一定要謹慎確認。
SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';
在資料庫如果要徹底殺掉一個會話，尤其是大事務會話，最好是使用ALTER SYSTEM DISCONNECT SESSION IMMEDIATE或使用下面步驟：
1：首先在作業系統級別Kill掉程式。
2：在資料庫內部KILL SESSION
或者反過來亦可。這樣可以快速終止程式，釋放資源。
參考資料：
http://www.eygle.com/faq/Kill_Session.htm
http://www.oracle-base.com/articles/misc/killing-oracle-sessions.php#unix
http://www.eygle.com/archives/2005/10/oracle_howto_kill_session.html
http://blog.sina.com.cn/s/blog_6d6e54f70100zfqp.html
http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_2013.htm#SQLRF53047
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29330852/viewspace-1725181/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle如何徹底kill掉會話及os作業系統資源Oracle會話作業系統
- Oracle 徹底 kill sessionOracleSession
- 【kill session】Oracle 徹底 kill session（轉載）SessionOracle
- Oracle徹底殺掉程式Oracle
- 【會話】Oracle kill session系列會話OracleSession
- 【Kill】兩條Linux命令徹底殺死OracleLinuxOracle
- Oracle kill會話--sed修改檔案Oracle會話
- MySQL:Innodb如何快速殺掉堵塞會話的思考MySql會話
- Kill會話過程分析會話
- MySQL: kill 會話的實現原理MySql會話
- 大話Oracle中的kill sessionOracleSession
- MySQL kill會話不起作用？MySql會話
- Kill會話過程分析（二）會話
- 徹底刪除ORACLEOracle
- win10徹底禁用svchost如何操作 win10怎麼徹底關掉svchostWin10
- 快速殺掉Oracle的SessionOracleSession
- securecrt保持會話不會斷掉Securecrt會話
- oracle rac 12徹底刪除,徹底刪除該死的racOracle
- kill執行時間較長的會話會話
- 批量kill殺死某些會話session的plsql會話SessionSQL
- win10如何徹底關閉windows update_徹底關掉win10自動更新的方法Win10Windows
- AIX徹底刪除ORACLEAIOracle
- 【前端】一文徹底學會Promise前端Promise
- session會話的底層實現方式Session會話
- 徹底解決Oracle中文亂碼Oracle
- Windows下徹底解除安裝ORACLEWindowsOracle
- RAC可以殺掉其他例項上的會話會話
- 怎麼殺掉特定的資料庫會話資料庫會話
- 更改SERVICE_NAME導致大量會話被KILL會話
- Kill session 和orakill 殺會話及程式總結Session會話
- kill掉 mysql 大量鎖表程式MySql
- 徹底幹掉 BeanUtils，最優雅的 Mapstruct 增強工具全新出爐BeanStruct
- ORA-00031: 標記要刪去的會話，但會話kill不掉解決辦法會話
- oracle徹底刪除資料檔案Oracle
- （個人）windowns系統徹底刪除oracleOracle
- 【recyclebin】徹底禁用 Oracle回收站功能Oracle
- 談談如何徹底解除安裝Oracle？Oracle
- oracle的會話如何自殺？Oracle會話