彻底搞清楚library cache lock的成因和解决方法(5)

问题的成因已经基本上明确了，这里推荐两种解决问题的方法：
方法1，根据 c000000122e2a6d8 地址，我们可以得到当前在library cache中相应的锁信息： SQL> l
1 select INST_ID,USER_NAME,KGLNAOBJ,KGLLKSNM,KGLLKUSE,KGLLKSES,KGLLKMOD,KGLLKREQ,KGLLKPNS,KGLLKHDL
2* from X$KGLLK where KGLLKHDL = 'C000000122E2A6D8' order by KGLLKSNM,KGLNAOBJ
SQL> /

INST_ID USER_NAME KGLNAOBJ KGLLKSNM KGLLKUSE KGLLKSES KGLLKMOD KGLLKREQ KGLLKPNS KGLLKHDL
---------- ------------- ---------------------- ---------- ---------------- ---------------- ---------- ---------- ---------------- ----------------
2 PUBUSER CSNOZ629926699966 30 C000000109F02C68 C000000109F02C68 0 2 00 C000000122E2A6D8
2 PUBUSER CSNOZ629926699966 37 C000000108C99E28 C000000108C99E28 3 0 00 C000000122E2A6D8

SQL>

按照Oracle推荐的做法，我们现在应该使用'alter system kill session'命令kill掉SID 37，结果得到了ORA-00031错误：
SQL> alter system kill session '37,2707';

alter system kill session '37,2707'
*
ERROR at line 1:
ORA-00031: session marked for kill

SQL>

检查SID 37的状态：
SQL> set linesize 150
SQL> col program for a50
SQL> select sid,serial#,status,username,program from v$session where sid=37;

SID SERIAL# STATUS USERNAME PROGRAM
---------- ---------- -------- ------------------------------ --------------------------------------------------
37 2707 KILLED PUBUSER sqlplus@cs_dc02 (TNS V1-V3)

SQL>
再次证实了我们最初的想法—— 有人在执行了某个需要运行很久的DDL（多数是语句效率低，当然不排除遭遇bug的可能），
然后没等语句结束就异常退出了会话。

这个例子中我们在上面的跟踪文件已经找到了该会话对应的操作系统进程(SPID)，如果在其他情况下，我们如何找到这种状态为'KILLED'
的操作系统进程号（SPID）呢？
下面给出了一个方法，可以借鉴：
SQL> l
1 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
7* and s.sid=37
SQL> /

USERNAME STATUS ADDR KSLLAPSC KSLLAPSN KSLLASPO KSLLID1R KS D
------------------------------ -------- ---------------- ---------- ---------- ------------ ---------- -- -
PUBUSER KILLED C000000109C831E0 41 15 16243 17

SQL>

x$ksupr.ADDR列的值对应了V$PROCESS 中的ADDR的值，知道了这个SPID的地址，找到这个操作系统进程(SPID)就简单了，例如：
SQL> select spid,pid from v$process where addr='C000000109C831E0';

SPID PID
------------ ----------
20552 26

SQL>

现在，我们只需要在操作系统上 kill 操作系统进程20552就可以了：
ora9i@cs_dc02:/ora9i > ps -ef | grep 20552
ora9i 20552 1 0 Jan 8 ? 0:01 oraclecsmisc2 (LOCAL=NO)
ora9i 14742 14740 0 17:19:02 pts/ti 0:00 grep 20552
ora9i@cs_dc02:/ora9i > kill -9 20552
ora9i@cs_dc02:/ora9i > ps -ef | grep 20552
ora9i 14966 14964 0 17:40:01 pts/ti 0:00 grep 20552
ora9i@cs_dc02:/ora9i >

再来检查一下SID 37的信息，我们看到这个会话是真的被kill掉了，
ora9i@cs_dc02:/ora9i > exit

SQL> select sid,serial#,status,username,program from v$session where sid=37;

no rows selected

SQL> l
1 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
7* and s.sid=37
SQL> /

no rows selected

SQL>

回到刚才hang住的会话，它已经恢复了正常操作，
并且我们已经得到了'ORA-04043: object CSNOZ629926699966 does not exist'这个正常的信息：
SQL> desc CSNOZ629926699966

ERROR:
ORA-04043: object CSNOZ629926699966 does not exist

SQL>

在开一个会话，测试一把:
ora9i@cs_dc02:/ora9i > sqlplus pubuser/pubuser

SQL*Plus: Release 9.2.0.4.0 - Production on Mon Jan 10 17:42:16 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Clusters options
JServer Release 9.2.0.4.0 - Production

SQL> set timing on
SQL> desc CSNOZ629926699966
ERROR:
ORA-04043: object CSNOZ629926699966 does not exist

SQL>
当发出命令'desc CSNOZ629926699966'的时候，我们看到系统立刻返回了ORA-04043: object CSNOZ629926699966 does not exist'信息，问题就此解决了。

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18921899/viewspace-1017541/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18921899/viewspace-1017541/