儲存過程裡呼叫編譯自己是什麼情況？

CREATE OR REPLACE procedure   test_kgllk  as
 begin
   execute immediate 'alter procedure test_kgllk compile';
 end;
/

SESSION 1:

apolloCRMG>exec test_kgllk;

hang在那，後臺出現了pin等待。

SESSION 2:

apolloCRMG>Alter session set events 'immediate trace name library_cache level 10';

Session altered.

檢視dump檔案：

BUCKET#33667 mtx=0x82e73958(0, 13877, 0):
  LIBRARY HANDLE:0x9981fad0 bid=33667 hid=d9fc8383 lmd=X pmd=S sta=VALD
  name=APOLLO.TEST_KGLLK
  hash=d0fa07e525588ed0eeb7e2e7d9fc8383 idn=0
  tim=03-08-2011 12:58:03  kkkk-dddd-llll=0000-009d-60bf
  exc=0 ivc=1 ldc=4 slc=2 lct=10 pct=16
  cbb=9 rpr=6 kdp=0 kep=0 bus=8 hus=8 dbg=0
  dmtx=0x9981fb78(0, 6, 0) mtx=0x9981fbe0(1060, 269, 0)
  nsp=TABL(01) typ=PRCD(07) llm=0 flg=KGHP/TIM/[00004800]
  lwt=0x9981fb58[0x9981fb58,0x9981fb58]
  pwt=0x9981fb38[0x7af194a8,0x7af194a8]
  ref=0x9981fb68[0x7883a9e0,0x765602e0]
    HANDLE REFERENCES:
    reference   handle   flags
    --------- ---------  -------------------
     7883a9e0  682f2338  DEP[01]
 timestamp=03-08-2011 12:58:03 whr=0
     765602e0  7a661840  DEP/INV[05]
 timestamp=03-08-2011 12:48:21 whr=8
    LOCK OWNERS:
        lock     user  session count mode       flags
    -------- -------- -------- ----- ---- ------------------------
    7af19530 b222a920 b222a920     1 X    CNB/[01]
    886e24b8 b222a920 b222a920     1 N    PNC/[02]
    PIN OWNERS:
         pin     user  session     lock count mode mask
    -------- -------- -------- -------- ----- ---- ----
    77706f90 b222a920 b222a920 886e24b8     3 S    0011
    PIN WAITERS:
         pin     user  session     lock count mode mask
    -------- -------- -------- -------- ----- ---- ----
    7af19438 b222a920 b222a920        0     0 X    0000
    LIBRARY OBJECT: 0x769c1480
    flg=EXS/LOC[0005] pfl=NST[0001] ssta=VALD
    DEPENDENCIES: count=2 size=16
    dependency#    table reference   handle position flags
    ----------- -------- --------- -------- -------- -------------------
              0 76bb14e8  76bb1228 886e51a8        0 DEP[01]
              1 76bb14e8  76bb1268 778c6908        0 DEP[01]
    PARAMETERS are used
    DATA BLOCKS:
    data#     heap  pointer    status pins change whr
    ----- -------- -------- --------- ---- ------ ---
        0 99bb45d0 769c1560 769c1450 I/P/A/-/-    0 NONE   00
        2 769c17a8 a5873bf8 a5873bc8 I/-/A/-/-    0 NONE   00
        4 6fcad290 a5872bf8 a5872bc8 I/P/A/-/-    1 NONE   00
        7 9a1ec8e0 a226cb50 a226cb20 I/-/A/-/-    0 NONE   00

有點類似“死鎖”的情況，這種等待比較絕望，最後會報錯超時。

因為我們看到等待pin的會話和持有pin的會話是同一個。