SQL> select * from v$version;
BANNER
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
create or replace procedure proc
is
begin
null;
end;
/
begin
proc;
execute immediate 'alter procedure proc compile';
end;
/
SQL> select sid from v$mystat where rownum=1;
SID
----------
134
SQL> create or replace procedure proc
2 is
3 begin
4 null;
5 end;
6 /
Procedure created.
SQL> begin
2 proc;
3 execute immediate 'alter procedure proc compile';
4 end;
5 /
-------------hang----------
SQL> select sid,event,blocking_session from v$session where sid=134;
SID EVENT BLOCKING_SESSION
---------- ------------------------------ ----------------
134 library cache pin 134
SQL> SELECT s.sid, kglpnmod "HOLD Mode", kglpnreq "Req Mode"
2 FROM v$session_wait w, x$kglpn p, v$session s ,v$process o
3 WHERE p.kglpnuse=s.saddr
4 AND kglpnhdl=w.p1raw
5 and w.event like '%library cache pin%'
6 and s.paddr=o.addr;
SID HOLD Mode Req Mode
---------- ---------- ----------
134 2 0
134 0 3
BANNER
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
create or replace procedure proc
is
begin
null;
end;
/
begin
proc;
execute immediate 'alter procedure proc compile';
end;
/
SQL> select sid from v$mystat where rownum=1;
SID
----------
134
SQL> create or replace procedure proc
2 is
3 begin
4 null;
5 end;
6 /
Procedure created.
SQL> begin
2 proc;
3 execute immediate 'alter procedure proc compile';
4 end;
5 /
-------------hang----------
SQL> select sid,event,blocking_session from v$session where sid=134;
SID EVENT BLOCKING_SESSION
---------- ------------------------------ ----------------
134 library cache pin 134
SQL> SELECT s.sid, kglpnmod "HOLD Mode", kglpnreq "Req Mode"
2 FROM v$session_wait w, x$kglpn p, v$session s ,v$process o
3 WHERE p.kglpnuse=s.saddr
4 AND kglpnhdl=w.p1raw
5 and w.event like '%library cache pin%'
6 and s.paddr=o.addr;
SID HOLD Mode Req Mode
---------- ---------- ----------
134 2 0
134 0 3
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/37279/viewspace-1874069/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/37279/viewspace-1874069/