Error: ORA-04062: timestamp of procedure has been changed

环境：rhel5.3 oracle 11.2.0.1.0

现象：

数据库1（db1）上过程p1通过dblink调用数据库2（db2）上的过程p2

当db2上p2中的内容改变后，重新编译后，再重新编译db1上的p1是没问题的，但运行p1时就回报错：  --注意是运行时报错

Error: ORA-04062: timestamp of procedure   has been changed

经过查询一些资料，得知是REMOTE_DEPENDENCIES_MODE = { TIMESTAMP | SIGNATURE } 导致的

11.2oracle文档解释：

REMOTE_DEPENDENCIES_MODEProperty Description 
Parameter type String 
Syntax REMOTE_DEPENDENCIES_MODE = { TIMESTAMP | SIGNATURE } 
Default value TIMESTAMP 
Modifiable ALTER SESSION, ALTER SYSTEM 

REMOTE_DEPENDENCIES_MODE specifies how Oracle should handle dependencies upon remote PL/SQL stored procedures.

Values:

•TIMESTAMP
The client running the procedure compares the timestamp recorded on the server-side procedure with the current timestamp of the local procedure and executes the procedure only if the timestamps match.

•SIGNATURE
Oracle allows the procedure to execute as long as the signatures are considered safe. This setting allows client PL/SQL applications to be run without recompilation.

以上的timestamp 是等价于dba_objests里面的timestamp 列值，测试：

SQL> create procedure p
as
begin
  null;
end;
/
Procedure created.
--注意：11.2.0.1.0的环境，创建完过程后，再次使用pl/sql developer查看时oracle直接给你增加了 or replace 变成：
create or replace procedure p as
begin
null;
end;

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.

SQL>  select last_ddl_time, timestamp from user_objects where object_name = 'P';

LAST_DDL_TIME    TIMESTAMP
------------------- -------------------
2013-07-28 14:12:12 2013-07-28:14:12:12

SQL> grant execute on P to public;
Grant succeeded.

SQL>  select last_ddl_time, timestamp from user_objects where object_name = 'P';

LAST_DDL_TIME    TIMESTAMP
------------------- -------------------
2013-07-28 14:28:45 2013-07-28:14:12:12

SQL> alter procedure P compile;
Procedure altered.

SQL> select last_ddl_time, timestamp from user_objects where object_name = 'P';

LAST_DDL_TIME    TIMESTAMP
------------------- -------------------
2013-07-28 14:33:48 2013-07-28:14:12:12

--这里使用pl/sql developer工具编译后，下面timestamp马上发生了变化，所以可以得出pl/sql developer工具编译时和sqlplus原理不一样

SQL> select last_ddl_time, timestamp from user_objects where object_name = 'P';

LAST_DDL_TIME    TIMESTAMP
------------------- -------------------
2013-07-28 14:35:05 2013-07-28:14:35:05

SQL> alter procedure P compile;
Procedure altered.

SQL> select last_ddl_time, timestamp from user_objects where object_name = 'P';

LAST_DDL_TIME    TIMESTAMP
------------------- -------------------
2013-07-28 14:36:26 2013-07-28:14:35:50

SQL> alter procedure P compile;
Procedure altered.

SQL> select last_ddl_time, timestamp from user_objects where object_name = 'P';

LAST_DDL_TIME    TIMESTAMP
------------------- -------------------
2013-07-28 14:36:41 2013-07-28:14:35:50

以上测试说明：

all_objects中的last_ddl_time包括了grant，同时也包括了revoke ；create procedure会直接变成 create or replace procedure ，使用pl/sql developer

会改变timestamp 值，但sqlplus 不会；如果过程的内容改变了，不管使用哪个工具timestamp都会改变

所以解决以上错误只有以下2种方式：

1：alter procedure xxx   compile;   --运行过程前提前把含有dblink过程编译一下

        2：alter session set remote_dependencies_mode=signature;

在p1的过程中在引用dblink过程之前增加：

EXECUTE IMMEDIATE 'alter session set remote_dependencies_mode=signature';

即可解决问题。

signature 基本意味着程序接口（过程名，参数类型或者模式），关于具体什么情况会改变signature，现在还不清楚，有资料说：

改变in/out 参数，或者表结构会导致signature改变！！，现在还没遇见，后续如遇signature报错问题会继续一一补充在下面！！