library cache pin与PROCEDURE的重建

09:40:18 SQL> drop procedure calling;

Procedure dropped.

09:40:18 SQL>
09:40:18 SQL> drop procedure pining;

Procedure dropped.

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

Procedure created.

09:40:18 SQL>
09:40:18 SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

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

Procedure created.

09:40:18 SQL>
09:40:18 SQL> col object_name for a30
09:40:18 SQL> select object_name,last_ddl_time from dba_objects where object_name in ('PINING','CALLING');

OBJECT_NAME LAST_DDL_TIME
------------------------------ -------------------
CALLING 2007-04-02 09:40:18
PINING 2007-04-02 09:40:18

09:40:18 SQL>
09:40:18 SQL> exec calling;

在Session 2执行授权：

SQL> set time on
09:40:22 SQL> grant execute on pining to sys;

Grant succeeded.

09:40:22 SQL>

我们看到Session 2的授权顺利通过，再转到Session 1：

09:40:18 SQL> exec calling;

PL/SQL procedure successfully completed.

09:41:18 SQL>
09:41:18 SQL> select object_name,last_ddl_time from dba_objects where object_name in ('PINING','CALLING');

OBJECT_NAME LAST_DDL_TIME
------------------------------ -------------------
CALLING 2007-04-02 09:40:18
PINING 2007-04-02 09:40:22

我们看到对象PINING的LAST_DDL_TIME已经变化。
看来Grant已经能够绕过了Library Cache Pin的竞争，这是Oracle10g的增强。

这个问题最早由网友dqpylf在阅读我的新书《深入浅出Oracle》，在不同版本中测试案例时发现，今天才有时间做一点探究，感谢dqpylf。

-The End-

（出处：清风网络学院）