library cache pin的简单的场景重现

来源:互联网 发布:淘宝立即购买灰色 编辑:程序博客网 时间:2024/03/29 00:23
测试library cache pin


[root@orahost ~]# su - oracle
[oracle@orahost ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Tue May 6 17:45:53 2008

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Welcome to Yau's oracle world !

this is a test environment

fighting deal friend !!!

SQL>
SQL>
SQL>
SQL> create or replace PROCEDURE pining
2 IS
3 BEGIN
4 NULL;
5 END;
6 /

Procedure created.

SQL> create or replace procedure calling
2 is
3 begin
4 pining;
5 dbms_lock.sleep(1000);
6 end;
7 /

Procedure created.


在第一个会话中 执行calling 存储过程 

exec calling

在第二个会话中对pining进行重新编译
alter procedure pining compile;

此时,我们发现第二个会话处于等待状态。

执行以下脚本




select Distinct /*+ ordered */ w1.sid waiting_session,
h1.sid holding_session,
w.kgllktype lock_or_pin,
od.to_owner object_owner,
od.to_name object_name,
oc.Type,
decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_held,
decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_requested,
xw.KGLNAOBJ wait_sql,xh.KGLNAOBJ hold_sql
from dba_kgllock w, dba_kgllock h, v$session w1,
v$session h1,v$object_dependency od,V$DB_OBJECT_CACHE oc,x$kgllk xw,x$kgllk xh
where
(((h.kgllkmod != 0) and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and
(((w.kgllkmod = 0) or (w.kgllkmod= 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr
And od.to_address = w.kgllkhdl
And od.to_name=oc.Name
And od.to_owner=oc.owner
And w1.sid=xw.KGLLKSNM
And h1.sid=xh.KGLLKSNM
And (w1.SQL_ADDRESS=xw.KGLHDPAR And w1.SQL_HASH_VALUE=xw.KGLNAHSH)
And (h1.SQL_ADDRESS=xh.KGLHDPAR And h1.SQL_HASH_VALUE=xh.KGLNAHSH) ;





select * from v$session_wait where sid=28

alter system kill session '28,22'


select pro.spid from v$session ses,v$process pro where ses.sid=28 and ses.paddr=pro.addr;


[oracle@orahost ~]$ ps -ef | grep 11302
oracle 11302 11301 0 17:53 ? 00:00:00 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 11461 11397 0 18:52 pts/7 00:00:00 grep 11302
[oracle@orahost ~]$ kill -9 11302



SQL> exec calling
BEGIN calling; END;

*
ERROR at line 1:
ORA-03113: end-of-file on communication channel



SQL> alter procedure pining compile;

Procedure altered.



0 0