--查询哪些会话有关于library cache pin/library cache lock 等待事件的信息【会话状态／进程／ＳＩＤ／用户．．等】
SQL> SELECT C.SPID,A.SID, A.SERIAL#, A.USERNAME, A.STATUS /*进程状态*/,A.OSUSER,A.MACHINE,A.PROGRAM,B.ADDR
FROM V$SESSION A, X$KGLPN B,V$PROCESS C
WHERE A.SADDR = B.KGLPNUSE
AND C.ADDR=A.PADDR
AND B.KGLPNMOD <> 0
AND B.KGLPNHDL IN
(SELECT P1RAW FROM V$SESSION_WAIT WHERE upper(EVENT) LIKE upper('LIBRARY CACHE%'))
/*AND A.STATUS= 'KILLED'*/;
SPID SID SERIAL# USERNAME STATUS OSUSER MACHINE PROGRAM ADDR
----- ---- ---------- ------------------ -------- -------------------- ---------------------------------- -------------------------- ----------------
11949 963 2079 MIDTEST_3 KILLED Administrator WORKGROUP\2012-20121008 OS plsqldev.exe 00007F85060DC8B8
若status=active，确认不适用后 可以选择用Oracle kill 或者OS kill 来释放资源
Oracle kill
alter system kill session '963,2079' immediate;
alter system kill session '963,2079' ;
操作系统kill [由于本环境status=killed，所以直接OS kill 如下]
[root@xcd ~]# ps -ef| grep 13309
oracle 11949 1 0 13:19 ? 00:00:00 oraclekfdb (LOCAL=NO)
root 15263 15236 0 15:38 pts/0 00:00:00 grep 11949
[root@xcd ~]# kill -9 11949
[root@xcd ~]# ps -ef| grep 11949
root 15259 15236 0 15:38 pts/0 00:00:00 grep 11949
[root@xcd ~]#
SQL> select a.spid,b.sid,b.serial#,b.username from v$process a,v$session b where a.addr=b.paddr and b.status= 'KILLED';
SPID SID SERIAL# USERNAME
---------------- ---------- ------------------------------
SQL>
再重新编译 问题解决了~
--多个会话同时操作可能引起的报错【 ORA-04021: 等待锁定对象 时发生超时】
--如果是多个的话查询如下SQL
select * from v$access where object = 'IES_INV_MTL_OUT_APPLY' ;
select kglnaown, kglnaobj, kglnadlk
from x$kglob t, v$session_wait sw
where t.kglhdadr = sw.p1raw
and sw.sid in (select sid
from v$session a, x$kglpn b
where a.saddr = b.kglpnuse
and b.kglpnmod <> 0
and b.kglpnhdl in
( select p1raw
from v$session_wait
where event like 'library cache%'));
