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


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


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

Other related posts: