处理Oracle中杀不掉的锁

一些ORACLE中的进程被杀掉后，状态被置为"killed"，但是锁定的资源很长时间不释放，有时实在没办法，只好重启数据库。现在提供一种方法解决这种问题，那1.就是在ORACLE中杀不掉的，2.在OS一级再杀。

1.下面的语句用来查询哪些对象被锁：
select a.object_name,a.object_type,s.osuser,s.username,s.status,machine,s.sid,s.serial#
from v$locked_object l,dba_objects o ,v$session s ,all_objects a
where l.object_id　=　o.object_id and l.session_id=s.sid and a.object_id=l.OBJECT_ID



 
 
 

object_name: 被锁的对象名；object_type: 对象类型； osuser:主机名称； username:oracle链接用户名；

machine:完整主机名；

 

1.2 查询可能出现锁表的语句（orale10g支持正则）

 
 

2.下面的语句用来杀死一个进程：
alter system kill session '24,111'; (其中24,111分别是上面查询出的sid,serial#)

【注】以上两步，可以通过Oracle的管理控制台来执行。

3.如果利用上面的命令杀死一个进程后，进程状态被置为"killed"，但是锁定的资源很长时间没有被释放，那么可以在os一级再杀死相应的进程（线程），首先执行下面的语句获得进程（线程）号：
select spid, osuser, s.program
from v$session s,v$process p
where s.paddr=p.addr and s.sid=24 （24是上面的sid）

4.在OS上杀死这个进程（线程）：
1)在unix上，用root身份执行命令:
#kill -9 12345（即第3步查询出的spid）
2)在windows（unix也适用）用orakill杀死线程，orakill是oracle提供的一个可执行命令，语法为：
orakill sid thread
其中：
sid：表示要杀死的进程属于的实例名 就是你安装的oracle的sid名字如我的是 orcl
thread：是要杀掉的线程号，即第3步查询出的spid。
例：c:>orakill orcl 12345

====================================

Oracle中Kill session的研究

我们知道,在Oracle数据库中,可以通过kill session的方式来终止一个进程,其基本语法结构为:
alter system kill session ’sid,serial#’ ; 
 
被kill掉的session,状态会被标记为killed,Oracle会在该用户下一次touch时清除该进程.

我们发现当一个session被kill掉以后，该session的paddr被修改，如果有多个session被kill，那么多个session的paddr都被更改为相同的进程地址:
SQL> select saddr,sid,serial#,paddr,username,status from v$session where username is not null;

SADDR           SID    SERIAL# PADDR    USERNAME                       STATUS
-------- ---------- ---------- -------- ------------------------------ --------
542E0E6C         11        314 542B70E8 EYGLE                          INACTIVE
542E5044         18        662 542B6D38 SYS                            ACTIVE


SQL> alter system kill session ’11,314’;

System altered.

SQL> select saddr,sid,serial#,paddr,username,status from v$session where username is not null;

SADDR           SID    SERIAL# PADDR    USERNAME                       STATUS
-------- ---------- ---------- -------- ------------------------------ --------
542E0E6C         11        314 542D6BD4 EYGLE                          KILLED
542E5044         18        662 542B6D38 SYS                            ACTIVE


SQL> select saddr,sid,serial#,paddr,username,status from v$session where username is not null;

SADDR           SID    SERIAL# PADDR    USERNAME                       STATUS
-------- ---------- ---------- -------- ------------------------------ --------
542E0E6C         11        314 542D6BD4 EYGLE                          KILLED
542E2AA4         14        397 542B7498 EQSP                           INACTIVE
542E5044         18        662 542B6D38 SYS                            ACTIVE

SQL> alter system kill session ’14,397’;

System altered.

SQL> select saddr,sid,serial#,paddr,username,status from v$session where username is not null;

SADDR           SID    SERIAL# PADDR    USERNAME                       STATUS
-------- ---------- ---------- -------- ------------------------------ --------
542E0E6C         11        314 542D6BD4 EYGLE                          KILLED
542E2AA4         14        397 542D6BD4 EQSP                           KILLED
542E5044         18        662 542B6D38 SYS                            ACTIVE


在这种情况下,很多时候，资源是无法释放的,我们需要查询spid，在操作系统级来kill这些进程.但是由于此时v$session.paddr已经改变，我们无法通过v$session和v$process关联来获得spid那还可以怎么办呢?
我们来看一下下面的查询:
  SQL> SELECT s.username,s.status,
  2  x.ADDR,x.KSLLAPSC,x.KSLLAPSN,x.KSLLASPO,x.KSLLID1R,x.KSLLRTYP,
  3  decode(bitand (x.ksuprflg,2),0,null,1)
  4  FROM x$ksupr x,v$session s
  5  WHERE s.paddr(+)=x.addr
  6  and bitand(ksspaflg,1)!=0;


USERNAME                       STATUS   ADDR       KSLLAPSC   KSLLAPSN KSLLASPO       KSLLID1R KS D
------------------------------ -------- -------- ---------- ---------- ------------ ---------- -- -
                                        542B44A8          0          0                       0
                               ACTIVE   542B4858          1         14 24069                 0    1
                               ACTIVE   542B4C08         26         16 15901                 0    1
                               ACTIVE   542B4FB8          7         46 24083                 0    1
                               ACTIVE   542B5368         12         15 24081                 0    1
                               ACTIVE   542B5718         15         46 24083                 0    1
                               ACTIVE   542B5AC8         79          4 15923                 0    1
                               ACTIVE   542B5E78         50         16 24085                 0    1
                               ACTIVE   542B6228        754         15 24081                 0    1
                               ACTIVE   542B65D8          1         14 24069                 0    1
                               ACTIVE   542B6988          2         30 14571                 0    1

USERNAME                       STATUS   ADDR       KSLLAPSC   KSLLAPSN KSLLASPO       KSLLID1R KS D
------------------------------ -------- -------- ---------- ---------- ------------ ---------- -- -
SYS                            ACTIVE   542B6D38          2          8 24071                 0
                                        542B70E8          1         15 24081               195 EV
                                        542B7498          1         15 24081               195 EV
SYS                            INACTIVE 542B7848          0          0                       0
SYS                            INACTIVE 542B7BF8          1         15 24081               195 EV

16 rows selected.

 
我们注意,红字标出的部分就是被Kill掉的进程的进程地址.

简化一点,其实就是如下概念:
SQL> select p.addr from v$process p where pid <> 1 2 minus 3 select s.paddr from v$session s;
ADDR
--------
542B70E8
542B7498
 

Ok，现在我们获得了进程地址，就可以在v$process中找到spid，然后可以使用Kill或者orakill在系统级来杀掉这些进程.实际上,我猜测:当在Oracle中kill session以后, Oracle只是简单的把相关session的paddr 指向同一个虚拟地址.此时v$process和v$session失去关联,进程就此中断.然后Oracle就等待PMON去清除这些Session.所以通常等待一个被标记为Killed的Session退出需要花费很长的时间.如果此时被Kill的process，重新尝试执行任务，那么马上会收到进程中断的提示，process退出，此时Oracle会立即启动PMON来清除该session.这被作为一次异常中断处理.

评论

1 楼 dolphin_ygj 2009-04-20  

WINDOWS自带的无敌kill进程命令
问:怎么才能关掉一个用任务管理器关不了的进程？我前段时间发现我的机子里多了一个进程，只要开机就在，我用任务管理器却怎么关也关不了

答1:杀进程很容易，随便找个工具都行。比如IceSword。关键是找到这个进程的启动方式，不然下次重启它又出来了。顺便教大家一招狠的。其实用Windows自带的工具就能杀大部分进程：

　　c:>ntsd -c q -p PID

　　只有System、SMSS.EXE和CSRSS.EXE不能杀。前两个是纯内核态的，最后那个是Win32子系统，ntsd本身需要它。ntsd从2000开始就是系统自带的用户态调试工具。被调试器附着(attach)的进程会随调试器一起退出，所以可以用来在命令行下终止进程。使用ntsd自动就获得了debug权限，从而能杀掉大部分的进程。ntsd会新开一个调试窗口，本来在纯命令行下无法控制，但如果只是简单的命令，比如退出(q)，用-c参数从命令行传递就行了。NtsdNtsd 按照惯例也向软件开发人员提供。只有系统开发人员使用此命令。有关详细信息，请参阅 NTSD 中所附的帮助文件。用法:开个cmd.exe窗口，输入：

　　ntsd -c q -p PID

　　把最后那个PID，改成你要终止的进程的ID。如果你不知道进程的ID，任务管理器－>进程选项卡－>查看－>选择列－>勾上"PID（进程标识符）"，然后就能看见了。

答2：xp下还有两个好东东tasklist和tskill。tasklist能列出所有的进程，和相应的信息。tskill能查杀进程，语法很简单：tskill 程序名！！


附：NTSD 中所附的帮助文件

usage: ntsd [-?] [-2] [-d] [-g] [-G] [-myob] [-lines] [-n] [-o] [-s] [-v] [-w]
[-r BreakErrorLevel] [-t PrintErrorLevel]
[-hd] [-pd] [-pe] [-pt #] [-pv] [-x | -x{e|d|n|i} ]
[-- | -p pid | -pn name | command-line | -z CrashDmpFile]
[-zp CrashPageFile] [-premote transport] [-robp]
[-aDllName] [-c "command"] [-i ImagePath] [-y SymbolsPath]
[-clines #] [-srcpath SourcePath] [-QR machine] [-wake ]
[-remote transport:server=name,portid] [-server transport:portid]
[-ses] [-sfce] [-sicv] [-snul] [-noio] [-failinc] [-noshell]

where: -? displays this help text
command-line is the command to run under the debugger
-- is the same as -G -g -o -p -1 -d -pd
-aDllName sets the default extension DLL
-c executes the following debugger command
-clines number of lines of output history retrieved by a remote client
-failinc causes incomplete symbol and module loads to fail
-d sends all debugger output to kernel debugger via DbgPrint
-d cannot be used with debugger remoting
-d can only be used when the kernel debugger is enabled
-g ignores initial breakpoint in debuggee
-G ignores final breakpoint at process termination
-hd specifies that the debug heap should not be used
for created processes. This only works on Windows Whistler.
-o debugs all processes launched by debuggee
-p pid specifies the decimal process Id to attach to
-pd specifies that the debugger should automatically detach
-pe specifies that any attach should be to an existing debug port
-pn name specifies the name of the process to attach to
-pt # specifies the interrupt timeout
-pv specifies that any attach should be noninvasive
-r specifies the (0-3) error level to break on (SeeSetErrorLevel)
-robp allows breakpoints to be set in read-only memory
-t specifies the (0-3) error level to display (SeeSetErrorLevel)
-w specifies to debug 16 bit applications in a separate VDM
-x sets second-chance break on AV exceptions
-x{e|d|n|i} sets the break status for the specified event
-2 creates a separate console window for debuggee
-i ImagePath specifies the location of the executables that generated
the fault (see _NT_EXECUTABLE_IMAGE_PATH)
-lines requests that line number information be used if present
-myob ignores version mismatches in DBGHELP.DLL
-n enables verbose output from symbol handler
-noio disables all I/O for dedicated remoting servers
-noshell disables the .shell (!!) command
-QR <machine> queries for remote servers
-s disables lazy symbol loading
-ses enables strict symbol loading
-sfce fails critical errors encountered during file searching
-sicv ignores the CV record when symbol loading
-snul disables automatic symbol loading for unqualified names
-srcpath specifies the source search path
-v enables verbose output from debugger
-wake wakes up a sleeping debugger and exits
-y specifies the symbol search path (see _NT_SYMBOL_PATH)
-z specifies the name of a crash dump file to debug
-zp specifies the name of a page.dmp file
to use with a crash dump
-remote lets you connect to a debugger session started with -server
must be the first argument if present
transport: tcp | npipe | ssl | spipe | 1394 | com
name: machine name on which the debug server was created
portid: id of the port the debugger server was created on
for tcp use: port=
for npipe use: pipe=
for 1394 use: channel=
for com use: port=,baud=,
channel=
for ssl and spipe see the documentation
example: ... -remote npipe:server=yourmachine,pipe=foobar
-server creates a debugger session other people can connect to
must be the first argument if present
transport: tcp | npipe | ssl | spipe | 1394 | com
portid: id of the port remote users can connect to
for tcp use: port=
for npipe use: pipe=
for 1394 use: channel=
for com use: port=,baud=,
channel=
for ssl and spipe see the documentation
example: ... -server npipe:pipe=foobar
-premote transport specifies the process server to connect to
transport arguments are given as with remoting

Environment Variables:

_NT_SYMBOL_PATH=[Drive:][Path]
Specify symbol image path.

_NT_ALT_SYMBOL_PATH=[Drive:][Path]
Specify an alternate symbol image path.

_NT_DEBUGGER_EXTENSION_PATH=[Drive:][Path]
Specify a path which should be searched first for extensions dlls

_NT_EXECUTABLE_IMAGE_PATH=[Drive:][Path]
Specify executable image path.

_NT_SOURCE_PATH=[Drive:][Path]
Specify source file path.

_NT_DEBUG_LOG_FILE_OPEN=filename
If specified, all output will be written to this file from offset 0.

_NT_DEBUG_LOG_FILE_APPEND=filename
If specified, all output will be APPENDed to this file.

_NT_DEBUG_HISTORY_SIZE=size
Specifies the size of a server's output history in kilobytes

Control Keys:

Quit debugger
Break into Target
Force a break into debuggee (same as Ctrl-C)
Debug Current debugger
Toggle Verbose mode
Print version information
ntsd: exiting - press enter ---