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

Search Discussions

  • Thomas Day at Dec 26, 2003 at 6:44 pm
    public synonym?

    To: Multiple recipients of list ORACLE-L
    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: 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).
  • Ryan_oracle_at_cox.net at Dec 26, 2003 at 6:44 pm
    I figured it out.

    I have another problem. I create my materialized view. I now want to write a query that joins it to a transactional table. I want to use query rewrite. Problem is the join is not on the primary key of either table.

    Is it possible to enable query rewrite without that? I have it in trusted mode?

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author:
    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).
  • Jared.Still_at_radisys.com at Dec 26, 2003 at 7:09 pm
    Warning: I have not actually used query rewrite in this way, so take this
    with a grain of salt.
    If you're joining the MV directly to a table, what is there to rewrite?
    If you were joining the tables that make up the MV, and doing so
    on the same key that was used to create the MV, and joining
    that result to a transactional table, it would make sense to use
    query rewrite.
    Based on your statement though, I don't see the need.
    Clarification?
    Jared

    Sent by: ml-errors@fatcity.com
    12/26/2003 10:44 AM
    Please respond to ORACLE-L

    To:        Multiple recipients of list ORACLE-L
    cc:
    Subject:        Re: please help with materialized view question

    I figured it out.
    I have another problem. I create my materialized view. I now want to write a query that joins it to a transactional table. I want to use query rewrite. Problem is the join is not on the primary key of either table.
    Is it possible to enable query rewrite without that? I have it in trusted mode?
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author:

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author:
    INET: Jared.Still_at_radisys.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).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedDec 26, '03 at 5:54p
activeDec 26, '03 at 7:09p
posts4
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase