8 Replies Latest reply on Mar 31, 2020 12:41 PM by Mark Reichman

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

    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