kill session V$SESSION標記為KILLED 的2種情況

gaopengtttt發表於2015-01-26
If the session is not making a SQL call to Oracle (is INACTIVE) when it is 
terminated, the ORA-28 message is not returned immediately.  The message is not
returned until the user subsequently attempts to use the terminated session.

When an inactive session has been terminated, STATUS in the view V$SESSION is 
"KILLED." The row for the terminated session is removed from V$SESSION after 
the user attempts to use the session again and receives the ORA-28 message.

If an active session cannot be interrupted (for example, it is performing 
network I/O or rolling back a transaction), the session cannot be terminated
until the operation completes.  In this case, the session holds all resources
until it is terminated.  Additionally, the session that issues the ALTER SYSTEM
statement to terminate a session waits up to 60 seconds for the session to
be terminated; if the operation that cannot be interrupted continues past 
one minute, the issuer of the ALTER SYSTEM statement receives a message 
indicating that the session has been "marked" to be terminated. A session 
marked to be terminated is indicated in V$SESSION with a status of "KILLED"
and a server that is something other than "PSEUDO."
 
根據上述文件,可以理解為2種情況
1、會話沒有佔用過多的資源,不需要釋放。這種情況一旦ALTER SESSION KILLED SESSION後需要客戶端再次
發起命令返回錯誤,會話即可釋放
ERROR at line 1: ORA-00028: your session has been killed
如果不拋錯會話一直未KILLED 狀態
可以加上IMMEDIATE子句來避免這樣的問題 
2、如果會話佔用過多資源,ORACLE KILLED發起者會等待60秒,如果資源不能釋放完成,那麼此會話會標記為KILLED
等待資源釋放完成。此時KILLED發起者會話將收到一個返回為會話標記為KILLED。這種情況下加入IMMEDIATE應該也是沒用的。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/7728585/viewspace-1415364/,如需轉載,請註明出處,否則將追究法律責任。

相關文章