That error usually means, either...
You've changed the definition of a DB Link that one of the slave MViews is referring to. In which case, you'll have to rebuild the MView.
You are using a DB Link on many schemas on the same DB, but with the same name but on different schemas. If you need to refresh some materialized views through a db link on many schemas on the same database, be sure to give a different name to your db links. If you don't you could get your error. Here's an old article, that mentions it
This is normal standard behavior. Object is invalidated when one or more objects it depends on change. Oracle simply tells you package changed since MV was created. It is up to to investigate what changed and if it affects MVs. Refreshing second (and subsequent) time will succeed.
Solomon - That is a system package. It seems to change all the time and I wont be able to recompile the package since I do not have DBA privs.
Paulzip - I read that same post about the db link names which is exactly what we have going on. Each user has a similarly name db link with different user and database inside. We did that so migrations could use the same db link name. I noticed that was a 9i issue and was thinking that surely would have been resolved by now.
I think I'm just going to recompile all the mviews before refreshing allowing them to pick the new package signature or timestamp so that this does not happen.
Mark Reichman wrote:
I noticed that was a 9i issue and was thinking that surely would have been resolved by now.
Unfortunately, that isn't always the case. It might be a deep rooted problem in the MView architecture, in which case, Oracle may never fix it. Oracle, like any organisation, weigh up the cost of re-engineering something that causes problems for certain end users, versus the inconvenience / likelihood / ease of workarounds for end users.
Hello Mark Reichman,
I would suggest to check for following parameter value and ensure you have the same in both source and target DBs:
If you have access to MOS, please read: ORA-4062 Explained (for Client and Server PL/SQL) (Doc ID 73506.1)
System package compile time can change when patching is done or someone explicitly recompiles them. What is LAST_DDL_TIME for DBMS_SNAPSHOT_UTL?
Everything seems to be
NAME TYPE VALUE
------------------------ ------ ---------
remote_dependencies_mode string TIMESTAMP
I may try ALTER SESSION SET REMOTE_DEPENDENCIES_MODE=SIGNATURE as recommended in the Doc ID 73506.1 at MOS.
Thanks everyone. It appears after one day of testing the doc id at MOS that Jesus pointed me to actually helped with this issue. I put the following line as the first line in my refresh proc and it seems to have fixed the issue. I did not compile anything as I stated above but just added this line at the top before issuing dbms_mview.refresh.
ALTER SESSION SET REMOTE_DEPENDENCIES_MODE=SIGNATURE