Re: creating MV logs in a logical standby DB
Date: Fri, 10 Dec 2010 06:58:55 -0800 (PST)
Message-ID: <c06699e1-f2e7-4beb-b903-8839614711a5_at_j29g2000yqm.googlegroups.com>
On Dec 9, 8:22 pm, Ora_DBA <sanjaydharmadhik..._at_gmail.com> wrote:
> I am trying to create materialized views based on a few tables in a
> logical standby database.
>
> The target database (11g R2) where the MVs will be created is a stand-
> alone database.
>
> The DB where the base tables reside is a logical standby database (11g
> R2).
>
> The requirement is to do a "FAST REFRESH" of the Materialized Views.
>
> My questions are :
>
> 1. Can I create MV logs in the logical standby DB?
> 2. If the answer to question no. 1 is "Yes", do I need to do anything
> different or configure the logical standby DB in a specific manner in
> order to create MV logs. From what I understand, the objects in the
> logical standby database are in a locked state. Is that going to be a
> problem ?
>
> Any other information that might be relevant is greatly appreciated.
>
> Thanks in advance.
I do not know what you can or cannot do in a logical standby but why would you want to create the MV logs in the logical standby instead of feed the MV from the primary database to begin with?
Your ability to do a fast refresh depends on the nature of the sql statement which you did not post or describe. Here are some of the restriction taken from the Oracle® Database Data Warehousing Guide 10g Release 2 (10.2) Part Number B14223-02:
•The materialized view must not contain references to non-repeating expressions like SYSDATE and ROWNUM.
•The materialized view must not contain references to RAW or LONG RAW data types.
•It cannot contain a SELECT list subquery.
•It cannot contain analytical functions (for example, RANK) in the SELECT clause.
•It cannot contain a MODEL clause.
•It cannot contain a HAVING clause with a subquery.
•It cannot contain nested queries that have ANY, ALL, or NOT EXISTS.
•It cannot contain a [START WITH …] CONNECT BY clause.
•It cannot contain multiple detail tables at different sites.
•On-commit materialized view cannot have remote detail tables.
•Nested materialized views must have a join or aggregate.
HTH -- Mark D Powell -- Received on Fri Dec 10 2010 - 08:58:55 CST