群里有个朋友问了个问题,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的解释如下:
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
The9.2.0.6databasehadbeenrebuiltfroma9.2.0.5database.
ItlookslikelythatthisissueisrelatedtoBug:3017255.
Itappearsthatisispossibleforinformationtobeflushedfromthesharedpoolwhichcannotbereloadedcorrectly.Byforcingaflushofthesharedpoolonbothsidestheproblemcanbeworkedaround.
Onboththemasterandthesourcesidesissue
altersystemflushshared_pool;
altersystemflushshared_pool;
Thisshouldallowtherefreshtostartandcompletesuccessfully.
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