记录一次解决lobrary cache lock 的过程.还比较顺利。但不知道为什么造成阻塞的那个会话会有这样的行为，不知如何深入研究。

AIX51, ORACLE9205
窗口A

在窗口A执行删除一些索引的命令，到最后一个语句时，会话挂起来了。
SQL> show user;
USER is "NCTELLING"
SQL> drop index I_ARAP_DJFB_JSZC_02;

Index dropped.

SQL> set autot off;
SQL> drop index I_ARAP_DJZB_JSZC_01;

Index dropped.

SQL> drop index I_BD_CUMANDOC_JSZC_01;

Index dropped.

SQL> drop index I_BD_CUMANDOC_JSZC_02;

Index dropped.

SQL> drop index I_IC_GENERAL_B_JSZC_04;

Index dropped.

SQL> drop index I_IC_GENERAL_H_JSZC_04;

Index dropped.

SQL> drop index I_SO_SALE_JSZC_02; -- 执行此语句时挂住了。

Index dropped.

SQL>

于是开辟窗口B，查询查窗口A的会话号，PID=162，执行

ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 10';

之后FTP,在/oracle/admin/xxxxx/udump 目录下载 DUMP 文件 ncdb_ora_5415070.trc, 在此文件里查找: PROCESS 162

查找PROCESS 162下的信息:

waiting for 'library cache lock' blocking sess=0x0 seq=829 wait_time=0
handle address=700000172bb6ec8, lock address=70000017a35ee08, 100*mode+namespace=12d

然后再查找关键字 700000172bb6ec8, 查找到信息

LIBRARY OBJECT PIN: pin=70000018aea2728 handle=700000172bb6ec8 mode=S lock=700000182501d40
user=70000016e805e20 session=70000016e805e20 count=1 mask=0f01 savepoint=106053 flags=[00]

找到这些信息后, 再找出这些信息所属的 PROCESS ID, 也就是PID, 为296,
根据PID=296, 找到SID=26, 发现这个会话已经被KILL了好几个小时, 不知道为什么, PMON
一直没清除掉这个会话. 之后找出这个会话的SPID, 在OS层再杀一次, 不久, 窗口A 自动
释放, 删除索引动作顺利完成.

窗口B

SQL> connect nctelling@ncdb
Enter password: ************
Connected.
SQL> set role dba;

Role set.

SQL> select sid from v$mystat group by sid;

SID
----------
111

SQL> select sid from v$session where terminal='ZHANG-HF';

SID
----------
111
161

SQL> select * from v$session_wait where sid=&sid;
Enter value for sid: 161
old 1: select * from v$session_wait where sid=&sid
new 1: select * from v$session_wait where sid=161

SID SEQ# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_TIME SECONDS_IN_WAIT STATE
---------- ---------- ------------------------------ ------------------------------ ---------- ---------------- ---------------------------------------------------------------- ---------- ---------------- ---------------------------------------------------------------- ---------- ---------------- ---------- --------------- -------------------
161 812 library cache lock handle address 5.0440E+17 0700000172BB6EC8 lock address 5.0440E+17 070000017A35EE08 100*mode+namespace 301 000000000000012D 0 307 WAITING

SQL> ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 10';

Session altered.

SQL> select paddr from v$session where sid=161;

PADDR
----------------
070000016E7100B8

SQL> select pid from v$process where addr='070000016E7100B8';

PID
----------
162

SQL> select addr from v$process where pid=296;

ADDR
----------------
070000016E739EB8

SQL> select sid from v$session where paddr='070000016E739EB8';

SID
----------
26

SQL> select a.serial#, last_call_et, status, taddr, spid from v$session a, v$process b
2 where sid=&sid and a.paddr=b.addr;
Enter value for sid: 26
old 2: where sid=&sid and a.paddr=b.addr
new 2: where sid=26 and a.paddr=b.addr

SERIAL# LAST_CALL_ET STATUS TADDR SPID
---------- ------------ -------- ---------------- ------------
651 11982 KILLED 5349500

SQL> select a.serial#, last_call_et, status, taddr, spid from v$session a, v$process b
2 where sid=&sid and a.paddr=b.addr;
Enter value for sid: 26
old 2: where sid=&sid and a.paddr=b.addr
new 2: where sid=26 and a.paddr=b.addr

SERIAL# LAST_CALL_ET STATUS TADDR SPID
---------- ------------ -------- ---------------- ------------
651 12001 KILLED 5349500

SQL>