Re: Materialized View refresh problem. 9i to 10g

On Nov 15, 9:12 am, Steve Paulson <codedra..._at_gmail.com> wrote:
> 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 re-
> granted 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

What does Metalink report for this? Anything? Have you considered submitting an SR?

David Fitzjarrell Received on Thu Nov 15 2007 - 09:33:41 CST