ORA-04021 timeout occurred while waiting to lock object stringstringstringstringstring Cause: While waiting to lock a library object, a timeout is occurred. Action: Retry the operation later. |
这很有可能是因为其他会话在调用该后台包,此时该包在共享池中被锁住。查询v$session_wait时会发现你的会话处于“library cache pin”等待。下面我们先再现一下该错误:
请输入用户名: yuechaotian/test 连接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production SQL> select distinct sid from v$mystat; SID ---------- 11 SQL> CREATE OR REPLACE PROCEDURE prc_test_lock 2 IS 3 BEGIN 4 WHILE TRUE LOOP 5 NULL; 6 END LOOP; 7 END; 8 / 过程已创建。 SQL> exec prc_test_lock; ... --处于死循环中 ... |
请输入用户名: yuechaotian/test 连接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production SQL> select distinct sid from v$mystat; SID ---------- 12 SQL> CREATE OR REPLACE PROCEDURE prc_test_lock 2 IS 3 v_temp VARCHAR2(10); 4 BEGIN 5 WHILE TRUE LOOP 6 NULL; 7 END LOOP; 8 END; 9 / ... -- 处于等待中,一段时间后,出现如下错误: CREATE OR REPLACE PROCEDURE prc_test_lock
|
SQL> conn / as sysdba SQL> select distinct sid from v$mystat; SID ---------- 19 SQL> select EVENT from v$session_wait where sid=12; EVENT ------------------- library cache pin |
我们接着在session3中查询(显示结果中的SYS为本次会话,可以排除):
SQL> SELECT DISTINCT '(' || s.sid || ') - ' || username AS "(session) - username" 2 FROM V$SESSION s, sys.x$kglob o, sys.x$kglpn p 3 WHERE upper(o.kglnaobj) LIKE upper('%prc_test_lock%') 4 AND p.kglpnhdl = o.kglhdadr 5 AND s.SADDR = p.kglpnuse; (session) - username ------------------------------------------------------ (11) - TEST (19) - SYS |
SQL> exec prc_test_lock; BEGIN prc_test_lock; END; * ERROR 位于第 1 行: ORA-00028: 您的会话己被删去 SQL> sys用户: SELECT DISTINCT s.sid || username |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7104/viewspace-1052452/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7104/viewspace-1052452/