三、测试存储过程的last_ddl_time和timestamp何时发生变化 select * from user_objects u where u.OBJECT_TYPE='PROCEDURE' AND U.OBJECT_NAME='PROC_JIFEN_CX' OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS PROC_JIFEN_CX [NULL] 559675 [NULL] PROCEDURE 2010-5-5 17:47:58.000 2010-9-16 22:19:31.000 2010-09-16:22:19:31 VALID / select * from user_objects u where u.OBJECT_TYPE='PROCEDURE' AND U.OBJECT_NAME='PROC_JIFEN_CX' / OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS PROC_JIFEN_CX [NULL] 559675 [NULL] PROCEDURE 2010-5-5 17:47:58.000 2010-11-5 11:38:09.000 2010-09-16:22:19:31 VALID / --存储过程状态为valid,编译存储过程,不改变内容,last_ddl_time改变,timestamp不变; alter table JIFEN_CX_LOG add nyytest number(2) / select * from user_objects u where u.OBJECT_TYPE='PROCEDURE' AND U.OBJECT_NAME='PROC_JIFEN_CX' / OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS PROC_JIFEN_CX [NULL] 559675 [NULL] PROCEDURE 2010-5-5 17:47:58.000 2010-11-5 11:38:09.000 2010-09-16:22:19:31 INVALID --对存储过程依赖的表进行修改,过程状态变为invalid select * from user_objects u where u.OBJECT_TYPE='PROCEDURE' AND U.OBJECT_NAME='PROC_JIFEN_CX' / --存储过程状态为invalid,编译存储过程,状态变为valid,last_ddl_time和timestamp都改为最新的 ---远程数据库调用执行报错ORA-04062: timestamp of procedure "EDU.PROC_JIFEN_CX" has been changed; 过程状态已为valid,再次执行成功,last_ddl_time和timestamp都改为最新的 OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS PROC_JIFEN_CX [NULL] 559675 [NULL] PROCEDURE 2010-5-5 17:47:58.000 2010-11-5 11:42:40.000 2010-11-05:11:42:40 VALID
|