An active session cannot be interrupted whenit is performing network I/O or rolling
back a transaction. Such a session cannot beterminated until the operation completes.
In this case, the session holds all resources until it is terminated. Additionally, the
session that issues the ALTER SYSTEMstatement to terminate a session waits up to 60
seconds for the session to be terminated. Ifthe operation that cannot be interrupted
continues past one minute, the issuer of the ALTER SYSTEMstatement receives a
message indicating that the session has been marked to be terminated. A session
marked to be terminated is indicated in V$SESSIONwith a status of KILLEDand a
session 2
back a transaction. Such a session cannot beterminated until the operation completes.
In this case, the session holds all resources until it is terminated. Additionally, the
session that issues the ALTER SYSTEMstatement to terminate a session waits up to 60
seconds for the session to be terminated. Ifthe operation that cannot be interrupted
continues past one minute, the issuer of the ALTER SYSTEMstatement receives a
message indicating that the session has been marked to be terminated. A session
marked to be terminated is indicated in V$SESSIONwith a status of KILLEDand a
server that is something other than PSEUDO.
一个执行network I/O与rollback的会话不能中断直到操作完成,执行alter system
kill ……的会话会在一分钟后收到被“ORA-00031: session marked for kill“的提示,
并且被kill的会话状态为killed。
下面我们以rollback为例进行说明
session 1:
SQL> insert into huang select * from huang;
100632 rows created.
SQL> /
201264 rows created.
SQL> /
402528 rows created.
SQL> /
805056 rows created.
SQL> /
1610112 rows created.
SQL> SELECT SID FROM V$MYSTAT WHERE ROWNUM =1
2 ;
SID
----------
147
SQL> delete from huang;
3220224 rows deleted.
SQL> rollback;
session 2
SQL> select SID ,SERIAL# , STATUS from v$session where username='HR';
SID SERIAL# STATUS
---------- ---------- ----------------
159 48 ACTIVE
SQL> show user
USER is "HR"
SQL> select SID ,SERIAL# , STATUS from v$session where username='HR';
SID SERIAL# STATUS
---------- ---------- ----------------
147 24 INACTIVE
159 48 ACTIVE
SQL> alter system kill session '147,24';
alter system kill session '147,24'
*
ERROR at line 1:
ORA-00031: session marked for kill
SQL> select SID ,SERIAL# , STATUS from v$session where username='HR';
SID SERIAL# STATUS
---------- ---------- ----------------
147 <span style="color:#ff0000;"> 24 KILLED</span>
159 48 ACTIVE