kill session V$SESSION标记为KILLED 的2种情况

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/，如需转载，请注明出处，否则将追究法律责任。