群里有个朋友问了个问题,Oracle 9i 的数据库,Job 执行失败,物化视图刷新报错。 错误信息如下:
ORA-04068: existing state of packages has been discarded
ORA-04062: of has been changed
ORA-04062: timestamp of package "SYS.DBMS_SNAPSHOT_UTL" has been changed
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 617
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 674
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 654
ORA-06512: at line 1
Oracle的解释如下:
Snapshot refresh of an 8.1.7 database from a 9.2.0.6 master
instance now fails for complete or fast refresh
exec dbms_snapshot.refresh('AS_GROUP','C') ;
BEGIN dbms_snapshot.refresh('AS_GROUP','C') ; END;
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04062: of has been changed
ORA-04062: timestamp of package "SYS.DBMS_SNAPSHOT_UTL" has been changed
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 617
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 674
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 654
ORA-06512: at line 1
The 9.2.0.6 database had been rebuilt from a 9.2.0.5 database.
It looks likely that this issue is related to Bug:3017255.
It appears that is is possible for information to be flushed from the shared pool which cannot be reloaded correctly. By forcing a flush of the shared pool on both sides the problem can be worked around.
On both the master and the source sides issue
alter system flush shared_pool;
alter system flush shared_pool;
This should allow the refresh to start and complete successfully.
BUG:3017255 - Create Materiarized View Caused Ora-4068 And Ora-4062
对于Bug 3017255, 相关信息如下:
PROBLEM:
--------
- Creating two materialized views using same database link name (owner is
different)
causes ORA-4068,ORA-4062.
- refreshing the materialized views alternately cause ORA-4062.
DIAGNOSTIC ANALYSIS:
--------------------
This only happens using same Database link name. (owner is differnet)
If change one side's Database link name, error does not be caused.
And if I execute 'alter system flush shared_pool' before create second
materialized view or refresh materialized views, error does not be caused.
WORKAROUND:
-----------
- Don't use same database link name even if owner is different.
- Execute 'alter system flush shared_pool' before create materialized views.
- Execute 'alter system flush shared_pool' before refresh materialized views