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.

On Dec 9, 8:22*pm, Ora_DBA <sanjaydharmadhik... (AT) gmail (DOT) com> wrote:
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 --

On Dec 9, 5:22*pm, Ora_DBA <sanjaydharmadhik... (AT) gmail (DOT) com> wrote:
According to http://www.scribd.com/doc/43930158/t...11gr2-1-131981
An Oracle Technical White Paper
September 2009
Oracle Data Guard with Oracle Database 11g Release 2

"Use SQL Apply if you meet its prerequisites and:
•
You wish to run reporting applications that require read-write access
to the standby database. Note that data maintained by SQL Apply
cannot be modified
•
You wish to add tables, additional schemas, indexes, and materialized
views to yourstandby database that do not exist on your primary
database"

I'm not quite sure if that answers your question, but at least you can
do it in the standby. For an extra license on primary and standby and
only on EE, of course.

jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2...om-ny-college/