This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,779 Users
  • 2,269,776 Discussions
  • 7,916,827 Comments

Discussions

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

Mark Reichman
Mark Reichman Member Posts: 624 Bronze Badge
edited Mar 31, 2020 8:41AM in SQL & PL/SQL

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

Tagged:
JesusBM-Oracle

Best Answer

  • JesusBM-Oracle
    JesusBM-Oracle Principal Technical Support Engineer Member Posts: 22 Employee
    edited Mar 30, 2020 2:04PM Answer ✓

    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

Answers

  • Paulzip
    Paulzip Member Posts: 8,867 Blue Diamond
    edited Mar 30, 2020 1:01PM

    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
    Solomon Yakobson Member Posts: 20,213 Red Diamond
    edited Mar 30, 2020 1:06PM

    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
    Mark Reichman Member Posts: 624 Bronze Badge
    edited Mar 30, 2020 1:19PM

    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
    Paulzip Member Posts: 8,867 Blue Diamond
    edited Mar 30, 2020 1:31PM
    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
    JesusBM-Oracle Principal Technical Support Engineer Member Posts: 22 Employee
    edited Mar 30, 2020 2:04PM Answer ✓

    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
    Solomon Yakobson Member Posts: 20,213 Red Diamond
    edited Mar 30, 2020 2:36PM

    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
    Mark Reichman Member Posts: 624 Bronze Badge
    edited Mar 30, 2020 3:56PM

    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
    Mark Reichman Member Posts: 624 Bronze Badge
    edited Mar 31, 2020 8:41AM

    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

    JesusBM-Oracle