please help with materialized view question

From: <ryan_oracle_at_cox.net>
Date: Fri, 26 Dec 2003 09:54:25 -0800
Message-ID: <F001.005DB19F.20031226095425@fatcity.com>

Im sure its a privilege issue.

I have 3 tables with two different owners
I want to create a materialized join view of these tables in a 3rd user account.
I altered the session to enable query rewrite and query_rewrite_integrity=trusted
I granted query rewrite enabled to every owner involved.
I can create the materialized view, if I do not join them to one of the owners or leave off 'query rewrite enabled.

Here is what I get.

create materialized view test
build immediate
refresh on demand
enable query rewrite
as
select columns

from user1.table1,
     user1.table2,
     user2.table3

where table1.pk = table2.pk
and table2.pk = table3.pk

ERROR at line 9:
ORA-00942: table or view does not exist

I have all privileges on this table otherwise. I can do a select, describe, create materialized view without query rewrite????

I take out 'query rewrite enabled' and it works.

I have granted query rewrite enabled to the user in question????

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: <ryan_oracle_at_cox.net
INET: ryan_oracle_at_cox.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Dec 26 2003 - 11:54:25 CST