alter system kill session中immediate字句的作用

SYS >select sid,serial#,status,server,inst_id from gv$session where username='APP';

       SID    SERIAL# STATUS   SERVER       INST_ID
---------- ---------- -------- --------- ----------
        20       1925 INACTIVE DEDICATED          1



不加immedate，kill session后查询gv$session，该会话记录STATUS是KILLED。

SYS >alter system kill session '20,1925,@1';
SYS >select sid,serial#,status,server,inst_id from gv$session where username='APP';

       SID    SERIAL# STATUS   SERVER       INST_ID
---------- ---------- -------- --------- ----------
        20       1925 KILLED   PSEUDO             1


在用户app的会话中操作，提示如下：
APP >select * from tab;
select * from tab
*
ERROR at line 1:
ORA-00028: your session has been killed


再查询gv$session，该会话记录已经没有了。
SYS >select sid,serial#,status,server,inst_id from gv$session where username='APP';

no rows selected




用app用户重新登录，再加上immediate子句操作：
SYS >select sid,serial#,status,server,inst_id from gv$session where username='APP';

       SID    SERIAL# STATUS   SERVER       INST_ID
---------- ---------- -------- --------- ----------
       243      15622 INACTIVE DEDICATED          1


SYS >alter system kill session '243,15622,@1' immediate;

再查询gv$session
SYS >select sid,serial#,status,server,inst_id from gv$session where username='APP';

no rows selected

这是可以看到gv$session已经没有记录了。


而app用户查询，直接提示连接断开了。
APP >select * from tab;
select * from tab
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 10189
Session ID: 243 Serial number: 15622



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的文档，在没有immediate字句情况下，oracle终结会话后，直到会话用户发起请求并提示会话终结后，才会恢复该会话状态。而immediate子句的作用是终结会话后，马上恢复该会话状态，所以前面例子操作后查询gv$session，已经没有该会话状态。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9871085/viewspace-715639/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9871085/viewspace-715639/