Materialized View refresh fails with ORA-04062: timestamp of package "SYS.DBMS_SNAPSHOT_UTL" has bee

Mark Reichman

Oracle 12.2

I have 12 identical oracle users with identical objects.  Each has identical mviews that refresh hourly through their own private db link each pointing to different user account on a different database.  For some reason only two of the 12 users get this error for this one particular mview - xvgld_us_states. I noticed when creating these mat views I would also get this ora-04062 sys.dbms_snapshot_utl error during mview creation as well.  when I issued the create statement a second or third time the create statement was successful.  Do I need to remotely compile this sys.dbms_snapshot_utl package remotely before refreshing?  Seems odd to have to do that.  Is it complaining about the remote sys.dbma_snapshot_utl or the local version?  Do I need to recompile all the materialized views before refreshing so this error does not occur?  I can manually refresh this mview and at times all works well.

ORA-12048: error encountered while refreshing materialized view "GLDRPT004"."XVGLD_US_STATES"

ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2952

ORA-04062: timestamp of package "SYS.DBMS_SNAPSHOT_UTL" has been changed

ORA-06512: at line 1

ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 85

ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 245

ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 1227

ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2414

ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2908

ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3191

ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3221

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 15

ORA-06512: at "GLDRPT004.MATERIALIZED_VIEW_CONTROL", line 31

JesusBM-Oracle

Hello @Mark Reichman,

I would suggest to check for following parameter value and ensure you have the same in both source and target DBs:

remote_dependencies_mode=SIGNATURE

If you have access to MOS, please read: ORA-4062 Explained (for Client and Server PL/SQL) (Doc ID 73506.1)

Best regards,

Jesus Botello-Mares

Paulzip

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.

-OR-

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

Solomon Yakobson

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.

SY.

Mark Reichman

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.

Paulzip

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.

JesusBM-Oracle

Hello @Mark Reichman,

I would suggest to check for following parameter value and ensure you have the same in both source and target DBs:

remote_dependencies_mode=SIGNATURE

If you have access to MOS, please read: ORA-4062 Explained (for Client and Server PL/SQL) (Doc ID 73506.1)

Best regards,

Jesus Botello-Mares

Solomon Yakobson

System package compile time can change when patching is done or someone explicitly recompiles them. What is LAST_DDL_TIME for DBMS_SNAPSHOT_UTL?

SY.

Mark Reichman

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.

Mark Reichman

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