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


Other related posts: