materialized view ON COMMIT REFRESH - 942 error

From: Tony Adolph <t_adolph_at_hotmail.com>
Date: 2005-12-28 18:57:30
Message-id: BAY113-F31E2F2E01359123AFD7A14FA360@phx.gbl

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

Received on Wed Dec 28 2005 - 18:57:30 CST