materialized view ON COMMIT REFRESH - 942 error 2005-12-28 - By Tony Adolph
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
|
|
