Re: Error while trying to create a Mview based on a remote Mview

From: GBA-DBA <gba.oraclel_at_gmail.com>
Date: Wed, 2 Mar 2011 12:26:34 -0600
Message-ID: <AANLkTi=DkC5L+3t58_TUtdm=e_xnQCMegu=NycCaCVLi_at_mail.gmail.com>



Hi Ellis,

About the qualified path: It creates the mview if I use select ... from owner.remote_mview_at_dblink, but I was expecting it to work without the prefix because the the read only schema used to establish the link has an after logon trigger that sets the current schema to the mview owner. Please correct me if my assumption is wrong.

About the local view: Yes it works without any prefix (just select ... from remote_mview_at_db_link)

Thanks for your help.

-- 
Regards
GBA

On Wed, Mar 2, 2011 at 11:45 AM, Ellis Miller <outlawdba_at_gmail.com> wrote:


> Have to assume fully qualified path is not the issue, that is, the local
> user trying to create the mview based on the remote mview is executing the
> DDL/SQL to build the local mview including the remote mview <owner>.MVIEW.
>
> Just curious, if you try to create a local view on the remote MVIEW does
> that work?
>
> Thanks.
>
>
> On Mon, Feb 28, 2011 at 5:23 PM, GBA-DBA <gba.oraclel_at_gmail.com> wrote:
>
>> Hi list,
>>
>> I have the following scenario (I don't know if what I'm trying here is
>> possible at all) :
>>
>> Oracle 11G 11.2.0.1.0 on AIX for both databases
>>
>> Remote DB
>> ----------------
>>
>> Application Owner: Two base tables, one MView (joining the two tables)
>> using refresh fast on commit.
>> Read only user: With select privilege (granted directly) on the base table
>> and the mview.
>>
>> Local DB
>> -----------------
>>
>> db link connecting to the read only user. Queries against the remote mview
>> work fine.
>>
>> Tried to create a mview based on the remote mview (using the query that
>> works in the previous step) but it fails with ORA-00942 table or view does
>> not exist.
>>
>> Have you guys faced this situation before? I was browsing this error and
>> looks like it may be related to a bug..but not sure.
>>
>> Thanks in advance for your help.
>>
>> Regards,
>> GBA
>>
>
>
>
> --
> Ellis R. Miller
> 937.830.8242
> 937.830.6027
>
> <http://my.wisestamp.com/link?u=2hxhdfd4p76bkhcm&site=www.wisestamp.com/email-install>
>
> Mundo Nulla Fides
>
>
>
> <http://my.wisestamp.com/link?u=gfbmwhzrwxzcrjqx&site=www.wisestamp.com/email-install>
>
>
>
>
>
>
-- http://www.freelists.org/webpage/oracle-l
Received on Wed Mar 02 2011 - 12:26:34 CST

Original text of this message