ORA-04021，timeout occurred while waiting to lock object

SQL> create or replace procedure binzhang_t is
  2  begin                                    
  3  dbms_output.put_line('hello');           
  4  dbms_lock.sleep(12000);                  
  5  end;                                     
  6  /                                        

Procedure created.

 

SQL> create or replace procedure binzhang_t is
  2  begin                                    
  3  dbms_output.put_line('hello');           
  4  dbms_lock.sleep(12000);                  
  5  end;                                     
  6  /                                        
create or replace procedure binzhang_t is
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object

Elapsed: 00:15:00.00

 

SQL> select event,SECONDS_IN_WAIT from v$session_wait where wait_class<>'Idle';

EVENT                          SECONDS_IN_WAIT
------------------------------ ---------------
SQL*Net message to client                    0
library cache pin                          100
SQL*Net more data from dblink          1196934
SQL*Net message to client                    0

網路上有人說是5分鐘，我實驗了下大約是15分鐘。

而且這個錯誤的提示，timeout occurred while waiting to lock object，比較讓人誤解是在獲取library cache lock的時候發生的，其實根據實驗看出pin也一樣會發生。