Materialized View refresh problem. 9i to 10g

Hello,
 I am having an issue with my materialized views that is driving me to distraction. I have two oracle instances, let's call them Oracle A and Oracle B.

Oracle A is running 10g Enterprise Edition Release 10.2.0.3.0 64Bit on AIX
Oracle B is 9i Enterprise Edition Release 9.2.0.8.0 32Bit on Windows

I have a database link defined from B to A and in A there is a specific user set up for this link. I have 2 dozen materialized views defined in B that each reference a single table in A. I have also define materialized view logs on each of the tables in A so I can do fast refreshes. I have granted full permissions on the materialized view logs to the database link user.

Sample Log definition (Instance A)
create materialized view log on MY_TABLE_NAME tablespace A_data1;

Here is a sample of one of the materialized view definitions.

create materialized view MY_TABLE_NAME
tablespace B_data1
nocache
logging
noparallel
build immediate
refresh fast start with sysdate next sysdate + 5/1440 as
select *
from OWNER.MY_TABLE_NAME_at_link_to_A;

Ok, so thats the situation, now here is the problem. The materialized views will happily go about their business of fast refreshing with no problem and then all of a sudden I will get this.

ORA-12012: error on auto execute of job 3074
ORA-12008: error in materialized view refresh path
ORA-01008: not all variables bound
ORA-02063: preceding line from link_to_A
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 820
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 877
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1

After tracking this issue for a few weeks I have found that it shows up more often when the data in the source table (Oracle A) is updated. Inserts don't seem to raise an issue. The worst part is that the issue is not consistent. I have experimented with it and sometimes it fails for the scheduled job and sometimes it doesn't. Sometimes it fails when executed manually and sometimes it doesn't.

At one point I thought maybe permissions were messed up so I regranted  the permissions to the materialized view logs in Oracle A and the problems went away in Oracle B but they came back later.

It happened again last night and when I got to work one of the materialized views was "broken". So I manually executed the DMBS_REFESH.REFRESH call for the materialized view it worked.

Help?!

Thank you in advance.
Steve Received on Thu Nov 15 2007 - 09:12:32 CST