RE: materialized view ON COMMIT REFRESH - 942 error

From: Goulet, Dick <DGoulet_at_vicr.com>
Date: 2005-12-28 20:06:42
Message-id: 4001DEAF7DF9BD498B58B45051FBEA6502FEE9BC@25exch1.vicorpower.vicr.com

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

Received on Wed Dec 28 2005 - 20:06:42 CST