同事修改存储过程trans_kc21时过程无法被编译被卡死
查V$DB_OBJECT_CACHE
SQL> SELECT * FROM V$DB_OBJECT_CACHE WHERE name=upper('trans_kc21') AND LOCKS!='0';
OWNER NAME DB_LINK NAMESPACE TYPE SHARABLE_MEM LOADS EXECUTIONS LOCKS PINS KEPT CHILD_LATCH INVALIDATIONS HASH_VALUE LOCK_MODE PIN_MODE STATUS TIMESTAMP PREVIOUS_TIMESTAMP LOCKED_TOTAL PINNED_TOTAL PROPERTY FULL_HASH_VALUE
---------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ------------ ---------- ---------- ---------- ---------- ---- ----------- ------------- ---------- --------- --------- ------------------- -------------------------------------- -------------------------------------- ------------ ------------ -------------------------------------------------------------------------------- --------------------------------
SICP3_GZYB TRANS_KC21 TABLE/PROCEDURE PROCEDURE 341712 34 1 1 1 NO 15307 0 1839741899 NULL SHARED VALID 2013-09-26/15:48:21 2013-09-26/15:46:17 47 68 356a3b5c43dd126d3e52acbe6da83bcb
发现locks＝1
按对象查出sid的值
v$access显示目前库缓存对象被锁定的信息,这个被强加的锁用来确保库缓存对象
在sql执行请求它时不会被老化
SQL> select SID from V$ACCESS WHERE object=upper('trans_kc21');
SID
----------
6566
查看session 状态
SQL> select a.SID,a.SERIAL#,b.SPID from v$session a,v$process b where a.SID=6566 and a.PADDR=b.ADDR;
SID SERIAL# SPID
---------- ---------- ------------------------
6566 27 23462626
kill这个会话
SQL>alter system kill session '6566,27';
如果这个会话已经是被kill的
SQL> select a.SID,a.SERIAL#,b.SPID,a.STATUS from v$session a,v$process b where a.SID=6566 and a.PADDR=b.ADDR;
SID SERIAL# SPID STATUS
---------- ---------- ------------------------ --------
6566 27 23462626 KILLED
就要在操作系统层来kill
[/@zzld03]#kill -9 23462626