Sometimes ora-04062 error occurs when database is running stored procedure

JavaScript implementation: how to write beautiful conditional expression>>>

The day before yesterday, a customer reported that sometimes ora-04062 errors occurred when the database was running stored procedures. The description of this error can be found in the Oracle documentation

This error originated in remote_ DEPENDENCIES_ The mode parameter is used to specify how the database processes the dependent objects for the procedure stored in remote PL/SQL. If it is set to timestamp, the process can only be executed if the server and local timestamps match. If it is set to signature, the process can be executed when the signature is secure. This parameter defaults to timestamp

Example 1: timestamp

PROC_ A (p_ ID in number)/* resides in db1 db1*/

PROC_ B (p_ Deptno in number)/* depends on proc_ A resides in db1 */

PROC_ C (p_ Deptno in number)/* rely on proc through dblink_ A resides in DB2 */

When proc_ When a is modified or compiled, the

ORA-04062:timestamp of procedure “XXXX.PROC_ A” has been changed

PROC_ B will fail immediately, proc_ C will fail at run time, and both processes must be recompiled to run correctly

To solve this problem, you can change the parameter to signature

ALTER system set REMOTE_ DEPENDENCIES_ MODE=’SIGNATURE’scope=BOTH

Example 2: Signature

PROC_ A (p_ ID in number)/* resides in db1 db1*/

PROC_ B (p_ Deptno in number)/* depends on proc_ A resides in db1 */

PROC_ C (p_ Deptno in number)/* rely on proc through dblink_ A resides in DB2 */

When proc_ When a is modified or compiled, the proc_ B and proc_ C construction remains valid, but when proc_ When the input parameter type of a changes, such as VARCHAR2, proc_ B and proc_ C will become invalid

Or use dynamic SQL to modify the process when you want to run it

ALTER SESSION SET REMOTE_ DEPENDENCIES_ MODE = {SIGNATURE | TIMESTAMP}

Similar Posts: