最常用的杀死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/15480802/viewspace-736634/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15480802/viewspace-736634/