Re: please help with materialized view question

From: Thomas Day <tday6_at_csc.com>
Date: Fri, 26 Dec 2003 10:44:27 -0800
Message-ID: <F001.005DB1A1.20031226104427@fatcity.com>

public synonym?

                                                                                                                                       
                      <ryan_oracle                                                                                                     
                      @cox.net>                To:      Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>                    
                      Sent by:                 cc:                                                                                     
                      ml-errors                Subject: please help with materialized view question                                    
                                                                                                                                       
                                                                                                                                       
                      12/26/2003 12:54                                                                                                 
                      PM                                                                                                               
                      Please respond                                                                                                   
                      to ORACLE-L

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).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Thomas Day
  INET: tday6_at_csc.com

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 - 12:44:27 CST