Oracle 9i ORA-04062 timestamp of package SYS DBMS_SNAPSHOT_UTL has been changed ORA-06512 at SYS DBMS_SNAPSHOT

群里有个朋友问了个问题，Oracle9i的数据库，Job执行失败，物化视图刷新报错。错误信息如下：

ORA-04068:existingstateofpackageshasbeendiscarded
ORA-04062:ofhasbeenchanged
ORA-04062:timestampofpackage"SYS.DBMS_SNAPSHOT_UTL"hasbeenchanged
ORA-06512:at"SYS.DBMS_SNAPSHOT",line617
ORA-06512:at"SYS.DBMS_SNAPSHOT",line674
ORA-06512:at"SYS.DBMS_SNAPSHOT",line654
ORA-06512:atline1

Oracle的解释如下：

Symptoms

Snapshotrefreshofan8.1.7databasefroma9.2.0.6master
instancenowfailsforcompleteorfastrefresh

execdbms_snapshot.refresh('AS_GROUP','C');
BEGINdbms_snapshot.refresh('AS_GROUP','C');END;
*
ERRORatline1:
ORA-04068:existingstateofpackageshasbeendiscarded
ORA-04062:ofhasbeenchanged
ORA-04062:timestampofpackage"SYS.DBMS_SNAPSHOT_UTL"hasbeenchanged
ORA-06512:at"SYS.DBMS_SNAPSHOT",line617
ORA-06512:at"SYS.DBMS_SNAPSHOT",line674
ORA-06512:at"SYS.DBMS_SNAPSHOT",line654
ORA-06512:atline1

Changes

The9.2.0.6databasehadbeenrebuiltfroma9.2.0.5database.

Cause

ItlookslikelythatthisissueisrelatedtoBug:3017255.

Itappearsthatisispossibleforinformationtobeflushedfromthesharedpoolwhichcannotbereloadedcorrectly.Byforcingaflushofthesharedpoolonbothsidestheproblemcanbeworkedaround.

Solution

Onboththemasterandthesourcesidesissue

altersystemflushshared_pool;

altersystemflushshared_pool;

Thisshouldallowtherefreshtostartandcompletesuccessfully.

References

BUG:3017255-CreateMateriarizedViewCausedOra-4068AndOra-4062

对于Bug3017255,相关信息如下：

PROBLEM:
--------
-Creatingtwomaterializedviewsusingsamedatabaselinkname(owneris
different)
causesORA-4068,ORA-4062.
-refreshingthematerializedviewsalternatelycauseORA-4062.

DIAGNOSTICANALYSIS:
--------------------
ThisonlyhappensusingsameDatabaselinkname.(ownerisdiffernet)
Ifchangeoneside'sDatabaselinkname,errordoesnotbecaused.
AndifIexecute'altersystemflushshared_pool'beforecreatesecond
materializedvieworrefreshmaterializedviews,errordoesnotbecaused.

WORKAROUND:
-----------
-Don'tusesamedatabaselinknameevenifownerisdifferent.
-Execute'altersystemflushshared_pool'beforecreatematerializedviews.
-Execute'altersystemflushshared_pool'beforerefreshmaterializedviews

<!--EndFragment-->