This is not a continuation of my Oracle Data Guard series, but I believe this topic is worth sharing.
Last Thursday, I received an email from one of my clients. They noticed that whenever a database program unit (e.g function, procedure) is recompiled, all its dependent objects become invalid. I was not surprised as I know that this is not a problem but that is how Oracle handles object dependencies.
Dependent objects keep a record of the timestamp of the object they are referencing. If a referenced object is recompiled or changed, its timestamp will differ from the value that was recorded on its dependent objects - this will mark the dependent objects as invalid. If the dependent object is on the same server as its referenced object, it will be invalidated immediately; however, if the dependent object resides on a different server (e.g referencing an object via database link), it will be invalidated only during runtime. Object dependency is controlled by the database parameter REMOTE_DEPENDENCIES_MODE, which is by default is set to TIMESTAMP.
Example 1: TIMESTAMP
Proc_A (p_id IN number) /* resides on DB1 */
Proc_B (p_deptno IN number) /* references Proc_A and resides on DB1 */
Proc_C (p_deptno IN number) /* references Proc_A using a DB link and resides on DB2 */
1.1 Proc_A was altered and recompiled.
Result:
ORA-04062: timestamp of procedure "XXXX.PROC_A" has been changed
Proc_B will be marked as invalid immediately. Proc_C will be not be invalidated immediately but only during runtime. Hence, both Proc_B and Proc_C need to be recompiled before they can be executed.
To avoid unneccesary invalidation, REMOTE_DEPENDENCIES_MODE should be set to SIGNATURE. When this setting is used, dependent objects will not be invalidated due to timestamp mismatch, but only when the referenced object's signature is changed (e.g a change in IN/OUT parameters, a change in table structure)
Example 2: SIGNATURE
Proc_A (p_id IN NUMBER) /* resides on DB1 */
Proc_B (p_deptno IN NUMBER) /* references Proc_A and resides on DB1 */
Proc_C (p_deptno IN NUMBER) /* references Proc_A using a DB link and resides on DB2 */
2.1 Proc_A was altered and recompiled.
Result:
Proc_B and Proc_C will remain valid.
2.2 Proc_A (p_id IN VARCHAR2) /* datatype was changed to VARCHAR2*/
Result:
Proc_B and Proc_C will be marked as invalid due to the change in Proc_A's signature.
Setting REMOTE_DEPENDENCIES_MODE to SIGNATURE
1. Login as a database user with DBA privilege and issue the issue the ff. command:
SQL> alter system set remote_dependencies_mode='SIGNATURE' scope=both;
Additional Notes:
The impact of setting REMOTE_DEPENDENCIES_MODE to SIGNATURE is not limited to server-side program units but also to other applications that use PL/SQL (e.g Oracle Forms).
When I was still an Oracle Forms developer and was assigned to apply application enhancements in our client's production environment, I had to recompile some Form modules for them to work properly (didn't realize that this timestamp mismatch issue also applies to Oracle Forms). With my limited DBA skills, I set the REMOTE_DEPENDENCIES_MODE to SIGNATURE and since then, I rarely encounter the said issue.
We should also be cautious when altering program units such as Packages. Package specs should NOT be recompiled if the modification was done on the package body only. Note that recompiling package specs will make all its dependent objects invalid.
