Oracle 9i ORA-04062 timestamp of package SYS dbms_snapshot_utl has been changed ORA-06512 at sys dBm

 

A friend in the group asked a question: the Oracle 9i database failed to execute the job, and the materialized view refresh reported an error. The error message is as follows:

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 is interpreted as follows:

Symptoms

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

Changes

The 9.2.0.6 database had been rebuilt from a 9.2.0.5 database.

Cause

It looks likely that this issue is related to bug: 3017255.

It appears that 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.

Solution

On both the master and the source sides issue

Alter system flush shared_pool;

Alter system flush shared_pool;

This shoshould allow the refresh to start and complete successfully.

References

BUG: 3017255-create materiarized view caused Ora-4068 and Ora-4062

For bug 3017255, the related information is as follows:

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