ALTER SYSTEM KILL SESSION does not Release Locks Killing a Thread on Windows NT [ID 100859.1]
来源:互联网 发布:励志类书籍推荐知乎 编辑:程序博客网 时间:2024/05/15 04:41
ALTER SYSTEM KILL SESSION does not Release Locks Killing a Thread on Windows NT [ID 100859.1]
ALTER SYSTEM KILL SESSION does not Release Locks Killing a Thread on Windows NT [ID 100859.1]
--------------------------------------------------------------------------------
修改时间 15-JUN-2012 类型 FAQ 状态 PUBLISHED
***Checked for relevance on 15-Jun-2012***
Problem Description
-------------------
Killing the session will not clear the locks. The session on the remote
database will remain idle waiting for input until the network read times
out. Only then the kill session is processed, and locks are released.
Solution Description
--------------------
Options:
========
You can shutdown and restart the database or use the ORAKILL utility to kill
threads.
Oracle has provided an ORAKILL utility that will kill shadow threads. Each
user's connection is represented by a thread in the Oracle process. If a
user's session is killed, then their Oracle session is killed - not the thread.
Oracle has provided an ORAKILL utility which can be passed a thread ID and will
kill the specified thread.
To make sure you do not kill a background process (which would crash your
database), you must perform a select to ensure you get the correct thread.
---
select p.spid "OS Thread", b.name "Name-User", s.osuser, s.program
from v$process p, v$session s, v$bgprocess b
where p.addr = s.paddr
and p.addr = b.paddr UNION ALL
select p.spid "OS Thread", s.username "Name-User", s.osuser, s.program
from v$process p, v$session s
where p.addr = s.paddr
and s.username is not null;
---
This will list all Shadow processes and backgound processes.
Each shadow process will show the thread ID - this is what must be killed via
the ORAKILL utility.
The kill session behavior is documented in the
Oracle® Database Administrator's Guide
11g Release 2 (11.2)
Part Number E25494-02
Section: Terminating Sessions
It does not explicitly indicate what happens to the session while it is
in the KILLED PSEUDO state. What's happening is that PMON
periodically checks to see if any sessions have been killed.
If it finds one, it attempts to rollback the transaction for that session
(that was in progress when it was killed). The reason this can take a long time is
because PMON may have more than one transaction to rollback at a time
(if other sessions have been killed, or if processes have died etc).
Thus, it may take a while to finally cleanup the killed session and have it
disappear from the session monitor. The system i/o monitor correctly shows
the reads and writes being performed by PMON in order to rollback the
session's transaction.
PMON will not delete the session object itself until the client connected to
that session notices that it has been killed. Therefore, the sequence of
events is:
1) alter system kill session is issued - the STATUS of the session object in
V$SESSION becomes KILLED, its server becomes PSEUDO.
2) PMON cleans up the *resources* allocated to the session
(i.e., rolls back its transaction, releases its locks, etc).
3) the entry in V$SESSION remains there until the client of that session (the
client is the process associated with the OSUSER,MACHINE,PROCESS columns in
the V$SESSION view) tries to do another request.
4) the client attempts another SQL statement and gets back ORA-28.
5) PMON can now remove the entry from V$SESSION.
This behavior is necessary because the client still has pointers to the
session object even though the session has been killed. Therefore, the
object cannot be deleted until the client is no longer pointing at it.
References
NOTE:69882.1 - Windows NT: Killing an Oracle Thread
NOTE:233884.1 - Killing Session does not release locks
- ALTER SYSTEM KILL SESSION does not Release Locks Killing a Thread on Windows NT [ID 100859.1]
- ALTER SYSTEM KILL SESSION
- ALTER SYSTEM KILL Session Marked for Killed Forever [ID 1020720.102]
- alter system kill session immediate
- orakill和ALTER SYSTEM KILL SESSION详解
- VMware Tools does not install on a Windows 2008 guest operating system after upgrading to ESX 4
- orakill和ALTER SYSTEM KILL SESSION有什么区别?
- orakill和ALTER SYSTEM KILL SESSION有什么区别?
- orakill和alter system kill session的区别
- orakill和alter system kill session的区别
- 找出 alter system kill session ‘sid,serial#’ kill 掉的数据库会话对应进程
- 找出 alter system kill session ‘sid,serial#’ kill 掉的数据库会话对应进程
- org.hibernate.HibernateException: No Hibernate Session bound to thread, and configuration does not a
- org.hibernate.HibernateException: No Hibernate Session bound to thread, and configuration does not a
- org.hibernate.HibernateException: No Hibernate Session bound to thread, and configuration does not a
- org.hibernate.HibernateException: No Hibernate Session bound to thread, and configuration does not a
- alter system ,alter database,alter session
- 【ORA】ORA-00030: User session ID does not exist.
- 小白文:将减、乘、除法转换为加法
- InvalidateRect()与Invalidate()的用法(转)
- wordpress 忽然出现 Internal Server Error
- 个人角度谈IE10浏览器
- CentOS下独立SVN服务器的安装与配置详细过程
- ALTER SYSTEM KILL SESSION does not Release Locks Killing a Thread on Windows NT [ID 100859.1]
- HDU 2243 Knight Moves
- 获取本机mac地址和Ip地址
- 新的开始
- 英雄与流氓
- ASP.NET
- hdu 3038 How Many Answers Are Wrong (带权并查集)
- My First Game(一)整体思路
- WORD自动引用参考文献