materialized view ON COMMIT REFRESH - 942 error 2005-12-28 - By Goulet, Dick
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@(protected)]
Sent: Wednesday, December 28, 2005 3:45 PM
To: Goulet, Dick; oracle-l@(protected)
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@(protected)>
To: <t_adolph@(protected)>; <oracle-l@(protected)>
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@(protected)
> [mailto:oracle-l-bounce@(protected)] On Behalf Of Tony Adolph
> Sent: Wednesday, December 28, 2005 12:57 PM
> To: oracle-l@(protected)
> 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 (See ORA-00942.ora-code.com): 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
|
|
