This discussion is archived
1 Reply Latest reply: May 15, 2012 9:38 PM by BillyVerreynne RSS

Materialized View and Materialized Log in different schemas & not working

900825 Newbie
Currently Being Moderated
I have two schemas A and B.

I created a materialized log in A like so:

create materialized view log on A.tran;

I then created a materialized view in B with refresh fast on commit:

create materialized view  MV
REFRESH FAST ON COMMIT
as select * from A.tran;

After that i try and commit data into the A's tran table but get this error:

ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist
12008. 00000 - "error in materialized view refresh path"
*Cause:    Table SNAP$_<mview_name> reads rows from the view
MVIEW$_<mview_name>, which is a view on the master table
(the master may be at a remote site). Any
error in this path will cause this error at refresh time.
For fast refreshes, the table <master_owner>.MLOG$_<master>
is also referenced.
*Action:   Examine the other messages on the stack to find the problem.
See if the objects SNAP$_<mview_name>, MVIEW$_<mview_name>,
<mowner>.<master>@<dblink>, <mowner>.MLOG$_<master>@<dblink>
still exist.


Can anyone explain why i am not able to get the materialized view to work in another schema?

Thanks in advance.

Edited by: 897822 on May 15, 2012 3:48 PM

Edited by: 897822 on May 15, 2012 3:49 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points