disconnect session和kill session的區別
最常用的殺死oracle程式的方法是alter system kill session ‘sid,serial#’。
但是今天遇到一些意外,資料庫有個從6月12號執行至今的sql,經客戶確認需要將其幹掉,登陸資料庫執行alter system kill。
於是有了以下情形
SQL> alter system kill session '137,7818';
alter system kill session '137,7818'
*
ERROR at line 1:
ORA-00031: session marked for kill
SQL> select status,event from v$session where sid = 137;
STATUS EVENT
-------- ----------------------------------------------------------------
KILLED SQL*Net more data from dblink
SQL> select object_id,locked_mode,session_id from v$locked_object;
OBJECT_ID LOCKED_MODE SESSION_ID
---------- ----------- ----------
165 3 137
104489 3 137
212 3 137
SQL> select TYPE,LMODE,REQUEST,BLOCK from v$lock where sid=137;
TY LMODE REQUEST BLOCK
-- ---------- ---------- ----------
JQ 6 0 0
JI 6 0 0
TM 3 0 0
TM 3 0 0
TM 3 0 0
TX 6 0 0
SQL> select t.status, s.status from v$transaction t, v$session s where s.taddr = t.addr and s.sid=137;
STATUS STATUS
---------------- --------
ACTIVE KILLED
該session已經被標誌為killed,但是其對應的transaction依舊為active,且對應的lock沒有被釋放;
又因為該instance由其他OS使用者啟動,當前登入的使用者沒有許可權執行kill -9
ora_10@justin_$ ps -ef | grep 15616
ora_xxx 15616 1 0 Jul 06 ? 0:22 ora_j001_GLIMSP
ora_10 20035 17648 0 08:23:18 pts/7 0:00 grep 15616
ora_10@justin_$ kill -9 15616
kill: 15616: permission denied
不是太清楚到底發生了什麼事情,但此時可使用disconnect session,請參考以下解釋
The KILL SESSION command doesn’t actually kill the session. It merely asks the session to kill itself. In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete. In these cases the session will have a status of “marked for kill”. It will then be killed as soon as possible.
The ALTER SYSTEM DISCONNECT SESSION syntax as an alternative method for killing Oracle sessions. Unlike the KILL SESSION command which asks the session to kill itself, the DISCONNECT SESSION command kills the dedicated server process (or virtual circuit when using Shared Sever), which is equivalent to killing the server process from the operating system. The basic syntax is similar to the KILL SESSION command with the addition of the POST_TRANSACTION clause. The SID and SERIAL# values of the relevant session can be substituted into one of the following statements.
The POST_TRANSACTION clause waits for ongoing transactions to complete before disconnecting the session, while the IMMEDIATE clause disconnects the session and ongoing transactions are recovered immediately.
http://fatihacar.com/blog/show-and-kill-transaction-lock-in-oracle/
SQL> alter system disconnect session '137,7818' immediate;
System altered.
SQL> select serial#,status,event from v$session where sid=137;
SERIAL# STATUS
---------- --------
EVENT
----------------------------------------------------------------
7822 ACTIVE
jobq slave wait
SQL> alter system disconnect session '137,7822' immediate;
System altered.
SQL> select serial#,status,event from v$session where sid=137;
no rows selected
SQL> select object_id,locked_mode,session_id from v$locked_object;
OBJECT_ID LOCKED_MODE SESSION_ID
---------- ----------- ----------
165 3 132
104489 3 132
212 3 132
SQL> select serial#,event,status,sql_id from v$session where sid=132;
SERIAL# EVENT
---------- ----------------------------------------------------------------
STATUS SQL_ID
-------- -------------
24231 jobq slave wait
ACTIVE
SQL> alter system disconnect session '132,24231' immediate;
System altered.
SQL> select object_id,locked_mode,session_id from v$locked_object;
no rows selected
--此時session被徹底清除,對應的lock也已釋放
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/25462274/viewspace-2057135/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- disconnect session和kill session的區別 轉Session
- [20180918]disconnect session和kill session的區別.txtSession
- alter system disconnect/kill session 'sid,serial#';Session
- kill sessionSession
- 【kill session】Oracle 徹底 kill session(轉載)SessionOracle
- cookie和session的區別CookieSession
- Session 和 Cookie 區別SessionCookie
- ALTER SYSTEM DISCONNECT SESSION ClauseSession
- awk -- kill sessionSession
- Oracle kill sessionOracleSession
- 【轉】Session ID/session token 及和cookie區別SessionCookie
- kill session的學問Session
- kill session的測試Session
- Alter system kill session and ORA-00031: session marked for killSession
- mysql批次kill sessionMySqlSession
- alter system kill sessionSession
- Oracle kill session scriptOracleSession
- oracle_kill_sessionOracleSession
- KILL SESSION 相關Session
- Oracle中Kill sessionOracleSession
- Oracle批量kill sessionOracleSession
- cookie 和session 的區別詳解CookieSession
- Cookie和Session的區別詳解CookieSession
- Cookie 和 Session 關係和區別CookieSession
- Redis分散式Session和普通的cookie session有什麼區別?Redis分散式SessionCookie
- 要 kill session 例子,session多,報錯如下Session
- Oracle中Kill session的研究OracleSession
- cookie和session的區別(全面總結)CookieSession
- cookie和session的詳解與區別CookieSession
- Session和Cookie的聯絡與區別SessionCookie
- PHP中session和cookie的區別薦PHPSessionCookie
- Session和Cookie的區別與聯絡SessionCookie
- 無腦批量kill sessionSession
- Oracle 徹底 kill sessionOracleSession
- 表鎖定,kill sessionSession
- Oracle中Kill session [轉]OracleSession
- cookie與session的區別CookieSession
- session與transaction的區別Session