RE: materialized view ON COMMIT REFRESH - 942 error

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_at_tiscali.de] Sent: Wednesday, December 28, 2005 3:45 PM To: Goulet, Dick; oracle-l_at_freelists.org 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"
To: ;
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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tony Adolph
> Sent: Wednesday, December 28, 2005 12:57 PM
> To: oracle-l_at_freelists.org
> 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
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

--
http://www.freelists.org/webpage/oracle-l