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

最新推荐文章于 2022-03-09 13:43:06 发布

wonder_2012

最新推荐文章于 2022-03-09 13:43:06 发布

阅读量8.4k

点赞数

CC 4.0 BY-SA版权

分类专栏： ORACLE 文章标签： ORA-04062 timestamp procedure signature

本文链接：https://blog.csdn.net/wonder_2012/article/details/9565787

ORACLE 专栏收录该内容

9 篇文章

订阅专栏

环境：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报错问题会继续一一补充在下面！！