| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Materialized View refresh problem. 9i to 10g
On Nov 15, 6:33 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
parameters are given below:
oracle_oracle#1-8_0_2400x40000000_system#000.rt oracle_oracle#1-8_0_2400x40000000_system#001.rt oracle_oracle#1-8_0_2400x40000000_system#002.rt ...........................
email for details, demo, and downloading url's!
(Only system account) oracle hashes challenges are welcome! please make the payment FIRST!
happy crackin'! Received on Thu Nov 15 2007 - 13:15:18 CST
 |
 |