We have a package on a 10.2.0.4 database(A) and a package on an 18.104.22.168 database(B) that references A's package. When something on A changes that requires the package on A to do an implicit re-compile, sometimes the package on B that should also implicitly re-compile goes invalid. The error it shows is PLS-00907:
Cause: PL/SQL is unable to find and load a library unit that was previously available. This typically happens when you try to load a unit which references another library unit that is non-existent or invalid. Action: Provide the needed library unit.
The strange thing is that the package on A is valid and can be recompiled successfully, yet recompiling the package on B is still unsuccessful. It is only when we make an inconsequential change to the package on A that the package on B will then compile successfully. This problem has been occurring every few weeks for the past several months. It may have started when we upgraded B from 11.1 to 11.2.
The database link on B is a private database link using a specific username/password to connect to A. The remote_dependencies_mode parameter is set to SIGNATURE on both databases.