kill session的学问

终止会话有两种方法:
1、alter system kill session 'sid,serial#' [ immediate ]
     在某个内存区域设置一个标记，表示这个session即将被kill，当session运行下一条命令时会检查此标记，若存在则session立即终止，同时收到"ORA-00028: your session has been killed"，session对应的server process也会立即终结，这可以解释为何我们kill一个活动session的时候往往能够成功，因为session在不断的和Server进行交互，很容易检测到即将被kill的标记。
     而对于非活动的session，相信大家都有下面的体验：当我们实施kill session命令之后，v$session.status显示状态为killed，但等了很长时间session还在，最后不得不在OS里kill掉server process才能将session真正kill掉，这是因为session处于非活动，无法及时检测到内存里的kill标记。
     alter system kill session的本质是session自己kill自己
     
2、alter system disconnect session 'sid,serial#' [immediate | post_transaction]
     其效果相当于直接kill掉OS的server process，相比kill session能够更加直接、快速的终止session，这是一种主动kill的方式。用上post_transaction选项可以等待当前正在执行的transaction提交或者回滚后再终止session，最大程度维持了disconnect session命令之前发起的transaction的完整性，这一功能是kill session命令所不具备的


下面来看几个例子加深印象，其中session 1 跑sql，session 2 发终止命令
=====================================================================
======================part 1 : alter system kill session 'sid,serial#' ====================
=====================================================================


<<<< 1、执行alter system kill session 'sid,serial#' 终止掉非活动的session >>>
---session 1:
SYS@tstdb1-SQL> select * from scott.t1116_1;


        RN
----------
        11
        22
         3
         4
         5


SYS@tstdb1-SQL> select s.sid,s.serial#,s.status,p.spid,s.program,s.server from v$session s,v$process p where sid=(select sys_context('userenv','sid')  from dual) and s.paddr=p.addr;


       SID    SERIAL# STATUS   SPID                     PROGRAM                                          SERVER
---------- ---------- -------- ------------------------ ------------------------------------------------ ---------
       994         39 ACTIVE   7864770                  sqlplus@jq570322b (TNS V1-V3)                    DEDICATED


SYS@tstdb1-SQL> update scott.t1116_1 set rn=33 where rn=3;


1 row updated.


---session 2: kill session
***session 1会话处于INACTIVE状态
select s.sid,s.serial#,s.status,p.spid,s.program,s.server,s.terminal from v$session s,v$process p where s.sid=994 and s.serial#=39 and s.paddr=p.addr(+);
       SID    SERIAL# STATUS   SPID                     PROGRAM                                          SERVER    TERMINAL
---------- ---------- -------- ------------------------ ------------------------------------------------ --------- ------------------------------
       994        39  INACTIVE                            sqlplus@jq570322b (TNS V1-V3)                    DEDICATED    pts/0
       
SYS@tstdb1-SQL> alter system kill session '994,39';   


System altered.


***下面的查询结果里spid为空值，SERVER变为了PSEUDO，原因是v$session.paddr值变了，其实server process在OS层面还存在的
select s.sid,s.serial#,s.status,p.spid,s.program,s.server,s.terminal from v$session s,v$process p where s.sid=994 and s.serial#=39 and s.paddr=p.addr(+);
       SID    SERIAL# STATUS   SPID                     PROGRAM                                          SERVER    TERMINAL
---------- ---------- -------- ------------------------ ------------------------------------------------ --------- ------------------------------
       994        39  KILLED                            sqlplus@jq570322b (TNS V1-V3)                    PSEUDO    pts/0


因为session 1处于INACTIVE状态所以其无法检测到kill标志，只能从OS层面kill掉server process 
kill -9 7406220


SYS@tstdb1-SQL> select s.sid,s.serial#,s.status,p.spid,s.program,s.server,s.terminal from v$session s,v$process p where s.sid=994 and s.serial#=39 and s.paddr=p.addr(+);


no rows selected         


<<<< 2、执行alter system kill session 'sid,serial#' 终止掉活动的session >>>
---session 1:
SYS@tstdb1-SQL> select s.sid,s.serial#,s.status,p.spid,s.program,s.server from v$session s,v$process p where sid=(select sys_context('userenv','sid')  from dual) and s.paddr=p.addr;


       SID    SERIAL# STATUS   SPID                     PROGRAM                                          SERVER
---------- ---------- -------- ------------------------ ------------------------------------------------ ---------
       532        137 ACTIVE   6619488                  sqlplus@jq570322b (TNS V1-V3)                    DEDICATED


***这里制造了一个百万数据量的表
create table scott.t1116_2 tablespace ts1116 as select * from dba_objects;
insert into scott.t1116_2 select * from t1116_2;
。。。执行若干次


***表里有250W的数据
SYS@tstdb1-SQL> select count(*) from scott.t1116_2;


  COUNT(*)
----------
   2562816
   
***执行循环update，运行大约1分钟       
declare
begin
while ( true ) loop
update scott.t1116_2 set object_name=dbms_random.string('u',100),edition_name=dbms_random.string('u',30),SUBOBJECT_NAME=dbms_random.string('u',30);
end loop;
end;
/


---session 2: kill session 
SYS@tstdb1-SQL> set timing on
SYS@tstdb1-SQL> alter system kill session '532,137';    <---不加immediate，kill耗时8s返回


System altered.


Elapsed: 00:00:08.00


session 2的kill session命令结束后，session 1随即显示
---session 1:
declare
*
ERROR at line 1:
ORA-00028: your session has been killed
ORA-00028: your session has been killed
ORA-06512: at line 4


不加immediate的kill session命令要等transaction rollback完成后才会返回，命令返回意味着session已经真正被kill掉了


<<<< 3、执行alter system kill session 'sid,serial#' immediate 终止活动的session >>>
---session 1:
SYS@tstdb1-SQL> select s.sid,s.serial#,s.status,p.spid,s.program,s.server from v$session s,v$process p where sid=(select sys_context('userenv','sid')  from dual) and s.paddr=p.addr;


       SID    SERIAL# STATUS   SPID                     PROGRAM                                          SERVER
---------- ---------- -------- ------------------------ ------------------------------------------------ ---------
       267       1295 ACTIVE   6423486                  sqlplus@jq570322b (TNS V1-V3)                    DEDICATED


***还是这张250W记录的数据表
SYS@tstdb1-SQL> select count(*) from scott.t1116_2;


  COUNT(*)
----------
   2562816
   
***执行循环update，这次执行时间延长到2分钟后再kill   
declare
begin
while ( true ) loop
update scott.t1116_2 set object_name=dbms_random.string('u',100),edition_name=dbms_random.string('u',30),SUBOBJECT_NAME=dbms_random.string('u',30);
end loop;
end;
/   


---session 2: 这次使用kill session ...  immediate
select s.sid,s.serial#,s.status,p.spid,s.program,s.server,s.terminal from v$session s,v$process p where s.sid=267 and s.serial#=1295 and s.paddr=p.addr(+);
       SID    SERIAL# STATUS   SPID                     PROGRAM                                          SERVER    TERMINAL
---------- ---------- -------- ------------------------ ------------------------------------------------ --------- ------------------------------
       267       1295 ACTIVE   6423486                  sqlplus@jq570322b (TNS V1-V3)                    DEDICATED pts/0


***可以看出kill session immediate后立即返回，得到的提示是"ORA-00031: session marked for kill"
SYS@tstdb1-SQL> set timing on
SYS@tstdb1-SQL> alter system kill session '267,1295' immediate;
alter system kill session '267,1295' immediate
*
ERROR at line 1:
ORA-00031: session marked for kill


Elapsed: 00:00:00.00


以上看出加了immediate的kill session命令是立即返回的，此时transaction rollback的动作还在后台继续执行，session何时被真正kill掉取决于rollback何时完成


---session 1:大约5秒后收到会话终止的消息
ERROR:
ORA-03114: not connected to ORACLE


declare
*
ERROR at line 1:
ORA-00028: your session has been killed
ORA-00028: your session has been killed
ORA-06512: at line 4


这里的5秒钟并不是固定的，transaction rollback的速度有快又慢，以下两种方法可以观察rollback的进度
***当session 1 对应的Transaction所使用的undo块数下降为0后，意味着会话被成功kill
SYS@tstdb1-SQL> select t.used_urec,t.used_ublk from v$transaction t,v$session s where s.sid=266 and s.serial#=1115 and s.saddr=t.ses_addr;


 USED_UREC  USED_UBLK
---------- ----------
   0      0
   
或者观察v$fast_start_transactions，当undoblocksdone=undoblockstotal时表示事务回滚结束
select undoblocksdone,undoblockstotal,xid from v$fast_start_transactions where xid='002B0009000015E7'  <--- XID取自于v$transaction.xid


=====================================================================
===================part 2 : alter system disconnect session 'sid,serial#'==================
=====================================================================

<<<< 4、执行alter system disconnect session 'sid,serial#' 终止非活动的session >>>
---session 1:
SYS@tstdb1-SQL> update scott.t1116_1 set rn=2 where rn=22;


1 row updated.


SYS@tstdb1-SQL> select s.sid,s.serial#,s.status,p.spid,s.program,s.server from v$session s,v$process p where sid=(select sys_context('userenv','sid')  from dual) and s.paddr=p.addr;
       SID    SERIAL# STATUS   SPID                     PROGRAM                                          SERVER
---------- ---------- -------- ------------------------ ------------------------------------------------ ---------
       469        895 ACTIVE   8455152                  sqlplus@jq570322b (TNS V1-V3)                    DEDICATED


---session 2:
SYS@tstdb1-SQL> alter system disconnect session '469,895' immediate;


System altered.


SYS@tstdb1-SQL> select s.sid,s.serial#,s.status,p.spid,s.program,s.server,s.terminal from v$session s,v$process p where s.sid=267 and s.serial#=21 and s.paddr=p.addr(+);


no rows selected


---session 1: 因为server process被kill了，所以session 1 下一次发起SQL命令时会直接收到ORA-03135的报错
SYS@tstdb1-SQL>
SYS@tstdb1-SQL>
SYS@tstdb1-SQL> select;
select
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 8455152
Session ID: 469 Serial number: 895


ERROR:
ORA-03114: not connected to ORACLE


<<<< 5、执行alter system disconnect session 'sid,serial#' 终止活动的session >>>
---session 1：
还是拿250W记录的表来说事
SYS@tstdb1-SQL> select count(*) from scott.t1116_2;


  COUNT(*)
----------
   2562816


SYS@tstdb1-SQL> select s.sid,s.serial#,s.status,p.spid,s.program,s.server from v$session s,v$process p where sid=(select sys_context('userenv','sid')  from dual) and s.paddr=p.addr;


       SID    SERIAL# STATUS   SPID                     PROGRAM                                          SERVER
---------- ---------- -------- ------------------------ ------------------------------------------------ ---------
       863        103 ACTIVE   6357528                  sqlplus@jq570322b (TNS V1-V3)                    DEDICATED


***运行以下过程2分钟左右       
declare
begin
while ( true ) loop
update scott.t1116_2 set object_name=dbms_random.string('u',100),edition_name=dbms_random.string('u',30),SUBOBJECT_NAME=dbms_random.string('u',30);
end loop;
end;
/


---session 2：disconnect session 
SYS@tstdb1-SQL> alter system disconnect session '863,103' immediate;
alter system disconnect session '863,103' immediate
*
ERROR at line 1:
ORA-00031: session marked for kill




Elapsed: 00:00:00.00


***观察used_ublk、used_urec逐渐下降，直到降为0后，session 1才退出
SYS@tstdb1-SQL> select t.used_urec,t.used_ublk,s.saddr,t.ses_addr,t.xid from v$transaction t,v$session s where s.sid=863 and s.serial#=103 and s.saddr(+)=t.ses_addr


 USED_UREC  USED_UBLK SADDR            SES_ADDR         XID
---------- ---------- ---------------- ---------------- ----------------
    279708       5940 07000001B67D7418 07000001B67D7418 002B001800001732


SYS@tstdb1-SQL> select t.used_urec,t.used_ublk,s.saddr,t.ses_addr,t.xid from v$transaction t,v$session s where s.sid=863 and s.serial#=103 and s.saddr(+)=t.ses_addr


 USED_UREC  USED_UBLK SADDR            SES_ADDR         XID
---------- ---------- ---------------- ---------------- ----------------
    246563       5237 07000001B67D7418 07000001B67D7418 002B001800001732


SYS@tstdb1-SQL> select t.used_urec,t.used_ublk,s.saddr,t.ses_addr,t.xid from v$transaction t,v$session s where s.sid=863 and s.serial#=103 and s.saddr(+)=t.ses_addr


 USED_UREC  USED_UBLK SADDR            SES_ADDR         XID
---------- ---------- ---------------- ---------------- ----------------
     77517       1644 07000001B67D7418 07000001B67D7418 002B001800001732


SYS@tstdb1-SQL> select t.used_urec,t.used_ublk,s.saddr,t.ses_addr,t.xid from v$transaction t,v$session s where s.sid=863 and s.serial#=103 and s.saddr(+)=t.ses_addr;


no rows selected


---session 1: 随即收到"ORA-00028: your session has been killed"


ERROR:
ORA-03114: not connected to ORACLE




declare
*
ERROR at line 1:
ORA-00028: your session has been killed
ORA-00028: your session has been killed
ORA-06512: at line 4


---session 2: 与kill session命令有所不同的是v$fast_start_transactions里没有记录下transaction rollback的过程，猜测可能是因为直接kill server process的缘故
SYS@tstdb1-SQL> select * from v$fast_start_transactions where xid='002B001800001732';


no rows selected


<<<< 6、执行alter system disconnect session 'sid,serial#' post_transaction终止非活动的session，session里含有未提交的事务 >>>
---session 1:
SQL> select * from scott.t1116_1;


        RN
----------
         1
         2
         3
         4
         5


---session 1:
set linesize 140
select s.sid,s.serial#,s.status,p.spid,s.program,s.server from v$session s,v$process p where sid=(select sys_context('userenv','sid')  from dual) and s.paddr=p.addr;
       SID    SERIAL# STATUS   SPID                     PROGRAM                                          SERVER
---------- ---------- -------- ------------------------ ------------------------------------------------ ---------
       266        229 ACTIVE   3081100                  sqlplus@jq570322b (TNS V1-V3)                    DEDICATED


SYS@tstdb1-SQL> update scott.t1116_1 set rn=11 where rn=1;


1 row updated.


---session 2:
alter system disconnect session '266,229';
SYS@tstdb1-SQL> alter system disconnect session '266,229';
alter system disconnect session '266,229'
                                      *
ERROR at line 1:
ORA-02000: missing POST_TRANSACTION or IMMEDIATE keyword     <---必须指定POST_TRANSACTION或者IMMEDIATE


***加了post_transaction    
SYS@tstdb1-SQL> alter system disconnect session '266,229' post_transaction;


System altered.


---session 1: 没有立即被kill掉，状态还是ACTIVE
SQL> select s.sid,s.serial#,s.status,p.spid,s.program,s.server from v$session s,v$process p where sid=(select sys_context('userenv','sid')  from dual) and s.paddr=p.addr;


       SID    SERIAL# STATUS   SPID                     PROGRAM                                          SERVER
---------- ---------- -------- ------------------------ ------------------------------------------------ ---------
       266        229 ACTIVE   3081100                  sqlplus@jq570322b (TNS V1-V3)                    DEDICATED


***server process健在
tstdb1@jq570322b:/home/tstdb1>ps -ef|grep 3081100 | grep -v grep
  tstdb1  3081100 12517530   0 10:51:17      -  0:00 oracletstdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
    
***甚至可以继续进行dml操作
SQL> update scott.t1116_1 set rn=22 where rn=2;


1 row updated.


SQL> select * from scott.t1116_1;
        RN
----------
        11
        22
         3
         4
         5
         
SQL> select s.sid,s.serial#,s.status,p.spid,s.program,s.server from v$session s,v$process p where sid=(select sys_context('userenv','sid')  from dual) and s.paddr=p.addr;


       SID    SERIAL# STATUS   SPID                     PROGRAM                                          SERVER
---------- ---------- -------- ------------------------ ------------------------------------------------ ---------
       266        229 ACTIVE   3081100                  sqlplus@jq570322b (TNS V1-V3)                    DEDICATED
       
***执行commit
SQL> commit;


Commit complete.


---session 2:此时去查v$session 发现 sid=266 and serial#=229的session已经消失
SQL> select s.sid,s.serial#,s.status,p.spid,s.program,s.server,s.terminal from v$session s,v$process p where s.sid=266 and s.serial#=229 and s.paddr=p.addr(+);


no rows selected


***server process在OS上也已经消失
ps -ef|grep 3081100 | grep -v grep |wc -l
       0


<<<< 7、执行alter system disconnect session 'sid,serial#' post_transaction终止非活动的session，session里没有未提交的事务 >>>
---session 1: 
SYS@tstdb1-SQL> select s.sid,s.serial#,s.status,s.program,p.spid,s.server from v$session s,v$process p where sid=(select sys_context('userenv','sid')  from dual) and s.paddr=p.addr;


       SID    SERIAL# STATUS   PROGRAM                                          SPID                     SERVER
---------- ---------- -------- ------------------------------------------------ ------------------------ ---------
       664         51 ACTIVE   sqlplus@jq570322b (TNS V1-V3)                    7406272                  DEDICATED


---session 2: disconnect session
SYS@tstdb1-SQL> select * from v$transaction;  <----当前没有任何活动事务


no rows selected


SYS@tstdb1-SQL> alter system disconnect session '664,51' post_transaction;


System altered.


***disconnect session后发现session还在，server process的spid已经变成空值了，SERVER从DEDICATED变成了PSEUDO
SYS@tstdb1-SQL> select s.sid,s.serial#,s.status,p.spid,s.program,s.server,s.terminal from v$session s,v$process p where s.sid=664 and s.serial#=51 and s.paddr=p.addr(+);


       SID    SERIAL# STATUS   SPID                     PROGRAM                                          SERVER    TERMINAL
---------- ---------- -------- ------------------------ ------------------------------------------------ --------- ------------------------------
       664         51 KILLED                            sqlplus@jq570322b (TNS V1-V3)                    PSEUDO    pts/0                                          


其实server process在OS层面还存在：
ps -ef|grep 7406272|grep -v grep
  tstdb1  7406272  7078198   0 13:06:56      -  0:00 oracletstdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))


至此大家发现与使用kill session命令的效果很像，此时有两种方法快速终结这个session，我们采用第二种方法
(1) 只要在session 1里发出任何有效的SQL命令都会收到"ORA-00028 your session has been killed"的错误，随即session和对应的server process会分别从数据库和OS级被终止
(2) OS里强行将server process进程kill掉：
kill -9 7406272


---session 2：查看session已经不存在：
SYS@tstdb1-SQL> select s.sid,s.serial#,s.status,p.spid,s.program,s.server,s.terminal from v$session s,v$process p where s.sid=664 and s.serial#=51 and s.paddr=p.addr(+);


no rows selected


---session 1:  再次执行任何SQL命令时会被提示ORA-03135，表明session已被彻底干掉
SYS@tstdb1-SQL> select ;
select
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 7406272
Session ID: 664 Serial number: 51




ERROR:
ORA-03114: not connected to ORACLE


<<<< 8、最后使用直接kill -9 server process命令的方法与disconnect session的作一个效果上的比较 >>>
---session 1：
SYS@tstdb1-SQL> select s.sid,s.serial#,s.status,p.spid,s.program,s.server from v$session s,v$process p where sid=(select sys_context('userenv','sid')  from dual) and s.paddr=p.addr


       SID    SERIAL# STATUS   SPID                     PROGRAM                                          SERVER
---------- ---------- -------- ------------------------ ------------------------------------------------ ---------
       863        107 ACTIVE   11796678                 sqlplus@jq570322b (TNS V1-V3)                    DEDICATED


***持续更新拥有250W记录的大表scott.t1116_2，持续时间约为2分钟
declare
begin
while ( true ) loop
update scott.t1116_2 set object_name=dbms_random.string('u',100),edition_name=dbms_random.string('u',30),SUBOBJECT_NAME=dbms_random.string('u',30);
end loop;
end;
/


***直接kill掉server process
kill -9 11796678


---session 1: 立即收到ORA-03113
ERROR:
ORA-03114: not connected to ORACLE




declare
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 11796678
Session ID: 863 Serial number: 107


---session 2: 连续查询几次，v$session还显示该session为ACTIVE
SYS@tstdb1-SQL> select s.sid,s.serial#,s.status,p.spid,s.program,s.server,s.terminal from v$session s,v$process p where s.sid=863 and s.serial#=107 and s.paddr=p.addr(+)


       SID    SERIAL# STATUS   SPID                     PROGRAM                                          SERVER    TERMINAL
---------- ---------- -------- ------------------------ ------------------------------------------------ --------- ------------------------------
       863        107 ACTIVE   11796678                 sqlplus@jq570322b (TNS V1-V3)                    DEDICATED pts/0


SYS@tstdb1-SQL> select s.sid,s.serial#,s.status,p.spid,s.program,s.server,s.terminal from v$session s,v$process p where s.sid=863 and s.serial#=107 and s.paddr=p.addr(+)


       SID    SERIAL# STATUS   SPID                     PROGRAM                                          SERVER    TERMINAL
---------- ---------- -------- ------------------------ ------------------------------------------------ --------- ------------------------------
       863        107 ACTIVE   11796678                 sqlplus@jq570322b (TNS V1-V3)                    DEDICATED pts/0


SYS@tstdb1-SQL> select s.sid,s.serial#,s.status,p.spid,s.program,s.server,s.terminal from v$session s,v$process p where s.sid=863 and s.serial#=107 and s.paddr=p.addr(+)


       SID    SERIAL# STATUS   SPID                     PROGRAM                                          SERVER    TERMINAL
---------- ---------- -------- ------------------------ ------------------------------------------------ --------- ------------------------------
       863        107 ACTIVE   11796678                 sqlplus@jq570322b (TNS V1-V3)                    DEDICATED pts/0


过了大约30秒，v$session里的session信息消失
SYS@tstdb1-SQL> select s.sid,s.serial#,s.status,p.spid,s.program,s.server,s.terminal from v$session s,v$process p where s.sid=863 and s.serial#=107 and s.paddr=p.addr(+);


no rows selected


之后在v$fast_start_transactions里发现Transaction正在rollback
SYS@tstdb1-SQL> select state,undoblocksdone,undoblockstotal from v$fast_start_transactions where xid='002700000000020C';


STATE            UNDOBLOCKSDONE UNDOBLOCKSTOTAL
---------------- -------------- ---------------
RECOVERING                 4747           10807


SYS@tstdb1-SQL> select state,undoblocksdone,undoblockstotal from v$fast_start_transactions where xid='002700000000020C'


STATE            UNDOBLOCKSDONE UNDOBLOCKSTOTAL
---------------- -------------- ---------------
RECOVERING                 5353           10807


SYS@tstdb1-SQL> select state,undoblocksdone,undoblockstotal from v$fast_start_transactions where xid='002700000000020C'


STATE            UNDOBLOCKSDONE UNDOBLOCKSTOTAL
---------------- -------------- ---------------
RECOVERING                 5856           10807


直到UNDOBLOCKSDONE=UNDOBLOCKSDONE，宣告rollback结束
SYS@tstdb1-SQL> select state,undoblocksdone,undoblockstotal from v$fast_start_transactions where xid='002700000000020C';


STATE            UNDOBLOCKSDONE UNDOBLOCKSDONE
---------------- -------------- ---------------
RECOVERED                 10807           10807


kill server process的命令是在OS层面发起的，server process被kill后之所以没有立即开始事务的回滚，是因为OS层产生的操作需要一定时间才能反馈到DB，也即何时能被pmon进程检测到，如果要缩短等待事件可以采用唤醒pmon进程的方法：
SYS@tstdb1-SQL> select s.program,p.pid from v$session s ,v$process p where s.program like '%PMON%' and p.addr=s.paddr;


PROGRAM                                                 PID
------------------------------------------------ ----------
oracle@jq570322b (PMON)                                   2


SYS@tstdb1-SQL> oradebug wakeup 2;


唤醒pmon进程后，transaction rollback就是立即开始，有兴趣的童鞋可以尝试一下


下面做一个总结
>>> alter system kill session:
        由于是session自己kill自己
        对于inactive的session，kill session后往往需要在OS级将其对应的server process 也kill掉；
        对于active的session，kill session后能够干净的kill掉对应的session和server process，如果不想在前台花太长时间等待transaction rollback结束，可以使用immediate选项快速返回命令行；


>>> alter system disconnect session:
post_transaction选项：
 (1) 会等待当前transaction结束后，终止掉session和对应的server process
 (2) 当没有活动transaction时，效果等同于kill session

immediate选项：
 (1) 如果被disconnect的session正处于活动状态，执行disconnect session命令的会话有可能收到"System altered"或者"ORA-00031: session marked for kill"的提示，如果是前者表示transaction已经回滚完成，更多遇到的是后者表示transaction还在后台执行Rollback，只不过先返回到了命令行提示符，等到transaction rollback结束后被disconnect的session才会收到"ORA-00028: your session has been killed"，disconnect session操作引起的transaction rollback只能通过v$transaction里的used_ublk和used_urec字段观察其进展，v$fast_start_transactions视图并没有记录;
 (2) 如果被disconnect的session处于非活动状态，那么会收到ORA-03135错误，而不像活动session那样会收到ORA-00028错误
无论上面哪种情况Session和server process都能被终止掉，不需要像kill session那样还要人工去kill server process


>>> 直接kill server process:
OS级直接kill掉server process后，需要等pmon进程检测到这一操作后transaction才会进行rollback，被kill session的真实状态会延时一段时间才能反应出来，减少延时可以采用oradebug wakeup来即时唤醒pmon


相比之下disconnect session的优势非常明显：快速清理、没有残留、使用post_transaction还能尽可能的不干扰正在执行的事务

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

转载于:http://blog.itpub.net/53956/viewspace-1839893/