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還能儘可能的不干擾正在執行的事務
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- kill sessionSession
- 【kill session】Oracle 徹底 kill session（轉載）SessionOracle
- Oracle kill session相關問題（上）OracleSession
- Oracle kill session相關問題（下）OracleSession
- Oracle中的Session kill不釋放問題OracleSession
- awk -- kill sessionSession
- Oracle kill sessionOracleSession
- kill session的測試Session
- Alter system kill session and ORA-00031: session marked for killSession
- disconnect session和kill session的區別Session
- mysql批次kill sessionMySqlSession
- alter system kill sessionSession
- Oracle kill session scriptOracleSession
- oracle_kill_sessionOracleSession
- KILL SESSION 相關Session
- Oracle中Kill sessionOracleSession
- Oracle批量kill sessionOracleSession
- Oracle中Kill session的研究OracleSession
- disconnect session和kill session的區別 轉Session
- 大話Oracle中的kill sessionOracleSession
- 無腦批量kill sessionSession
- Oracle 徹底 kill sessionOracleSession
- 表鎖定，kill sessionSession
- Oracle中Kill session [轉]OracleSession
- 要 kill session 例子，session多，報錯如下Session
- Oracle中Kill session的研究 for oracle -- 轉OracleSession
- 【會話】Oracle kill session系列會話OracleSession
- kill session V$SESSION標記為KILLED 的2種情況Session
- 批次kill session實現指令碼Session指令碼
- alter system kill session 不成功Session
- 定時kill長事務指令碼kill_long_session.sh指令碼Session
- 批量kill殺死某些會話session的plsql會話SessionSQL
- OB運維 | 連線 kill 中的 session_id運維Session
- windows下處理kill session不被釋放的鎖WindowsSession
- ALTER SYSTEM KILL SESSION這麼沒力？(轉)Session
- [20180918]disconnect session和kill session的區別.txtSession
- 輕鬆接觸Oracle資料庫中的Kill sessionOracle資料庫Session
- alter system disconnect/kill session 'sid,serial#';Session