oracle创建存储过程没反应

一 问题描述

执行创建存储过程没反应

二 监控是否被阻塞

#监控谁阻塞了谁
select  
(select username from v$session where sid=a.sid) blocker,a.sid, 'is blocking',
(select username from v$session where sid=b.sid) blockee,b.sid ,
(select sql_text from v$sqlarea c, v$session  d where c.sql_id=d.sql_id and d.sid=b.sid) blocked_sql_text
from v$lock a,v$lock b
where a.block=1 and b.request>0
and a.id1=b.id1
and a.id2=b.id2;

输出示例：

若上述sql查不到阻塞方sql，则查看下某会话是否被其他会话阻塞

#先取消存储过程的创建，查下当前会话的sid值：

SQL> select userenv('sid') from dual;

USERENV('SID')

--------------

305

 

select A.BLOCKING_SESSION_STATUS,A.BLOCKING_INSTANCE,A.BLOCKING_SESSION,A.EVENT FROM GV$SESSION A WHERE A.SID=305;

我这里发现有一个library cache pin的锁

#查看blocker是谁
SELECT s.sid, kglpnmod "Mode", kglpnreq "Req", SPID "OS Process"
FROM v$session_wait w, x$kglpn p, v$session s ,v$process o
WHERE p.kglpnuse=s.saddr
AND kglpnhdl=w.p1raw
and w.event like '%library cache lock%'
and s.paddr=o.addr
and kglpnmod =3;
       SID     Mode         Req OS Process
---------- ---------- ---------- ------------------------
       78       3           0 2807

#查看该会话当前执行的sql
select a.sid,a.program, a.username,b.spid, c.sql_text,c.SQL_ID
  from v$session a, v$process b, v$sqlarea c
 where a.paddr = b.addr
   and a.sql_hash_value = c.hash_value
   and a.username is not null
  and a.sid=sid值;
 
#杀死阻塞会话

SQL> select sid,serial# from v$session where sid=78;

       SID    SERIAL#
---------- ----------
    78    14427

SQL> alter system kill session '78,14427';

系统已更改。
 

--本篇文章参考了：https://blog.csdn.net/lihuarongaini/article/details/103331897