I believe the delete is necessary so that the refresh process can clean out the entries it just processed. -----Original Message----- From: NEW pop.tiscali.de [mailto:adolph.tony@xxxxxxxxxx] Sent: Wednesday, December 28, 2005 3:45 PM To: Goulet, Dick; oracle-l@xxxxxxxxxxxxx Subject: Re: materialized view ON COMMIT REFRESH - 942 error Hi Dick, as billing_api: grant select,delete on mlog$_xxx to tony; did the trick. Don't know if the delete is needed, will check tomorrow (in work)... Thanks for the feedback :-) Cheers Tony ----- Original Message ----- From: "Goulet, Dick" <DGoulet@xxxxxxxx> To: <t_adolph@xxxxxxxxxxx>; <oracle-l@xxxxxxxxxxxxx> Sent: Wednesday, December 28, 2005 8:06 PM Subject: RE: materialized view ON COMMIT REFRESH - 942 error > Tony, > > I believe that your problem has to do with the materialized view > log. Try the following: > > 1) as billing_api, grant select, delete on the mv log to tony > 2) as tony, create a synonym to the mv log using the same name. > > -----Original Message----- > From: oracle-l-bounce@xxxxxxxxxxxxx > [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Tony Adolph > Sent: Wednesday, December 28, 2005 12:57 PM > To: oracle-l@xxxxxxxxxxxxx > Subject: materialized view ON COMMIT REFRESH - 942 error > > Hi all, > > I've been reading this guide / that guide trying to get this problem > fixed, > but have failed :-( So I've created a simple example to illustrate my > error > (lack of knowledge). > > My master table, xxx is owned by billing_api and I want to replicate > this to > user tony. Both schemas (in my example) are on the same database. I > can't > create a fast refresh on commit MV. See the following: > > Version: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit > > The problem: > > connect billing_api > > create table xxx (id number not null, val varchar2(10)); > alter table xxx add constraint pk_xxx primary key (id) using index; > grant select on xxx to tony; > > create materialized view log on xxx; > > begin > for i in 1 .. 3 loop > insert into xxx (id,val) values (i, 'val'||i); > end loop; > end; > / > > select * from xxx; > > ID VAL > -------------------------------------- ---------- > 1 val1 > 2 val2 > 3 val3 > > connect tony > > select PRIVILEGE from user_sys_privs; > CREATE TABLE > CREATE SNAPSHOT > ON COMMIT REFRESH > > select * from billing_api.xxx; > ID VAL > -------------------------------------- ---------- > 1 val1 > 2 val2 > 3 val3 > > create materialized view xxx > build immediate > refresh fast on commit > as > select * from billing_api.xxx > / > 18:37:16 ORA-00942: table or view does not exist > > > At this point I did a lot of RTFMing, but couldn't resolve the problem. > I > then resulted to hacking and tried creating the MVLog with/without > PK/rowid, > with/without INCLUDING NEW VALUES, with/without SEQUENCE. But am > obviously > missing something key. > > Any pointers would be appreciated. At the moment I'm just trying to get > all > types of MVs working so that I can make a reasonably educated choice on > how > to use them later on. > > Any pointers to a good doc, would also be useful. > > TIA > Cheers > Tony > > > -- > //www.freelists.org/webpage/oracle-l > > > -- > //www.freelists.org/webpage/oracle-l > > -- //www.freelists.org/webpage/oracle-l