Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

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

Mark ReichmanMar 30 2020 — edited Mar 31 2020

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

This post has been answered by JesusBM-Oracle on Mar 30 2020
Jump to Answer
Comments
Post Details
Added on Mar 30 2020
8 comments
1,767 views