RE: Critical Qs on materialized views - Stale MV means?

Tim, Folks

Even after setting QUERY_REWRITE_INTEGRITY=STALE_TOLERATED on Both Source(8i) & Target(9i) databases, creation of materialized view Failing with the following error:-

SQL> create materialized view T1 on prebuilt table refresh complete   2 as select * from T1_at_S829mig_teja;
as select * from T1_at_S829mig_teja

          *
ERROR at line 2:
ORA-12014: table 'T1' does not contain a primary key constraint

SQL> show parameters query (for both databases)

NAME                                 TYPE        VALUE
------------------------------------ -----------
--------------------------
query_rewrite_enabled                string      false
query_rewrite_integrity              string      STALE_TOLERATED

NOTE
Master/Source = Oracle 8.1.7.4 DB
Slave/Target = 9.2.0 DB

Am I missing something / related parameter?

Thanks again

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman Sent: Friday, August 26, 2005 5:59 PM
To: Oracle-L
Subject: Re: Critical Qs on materialized views

ENFORCED constraints are used by MV refresh only for the purpose of guarding
against "staleness" as specified by the default setting of the parameter QUERY_REWRITE_INTEGRITY to the value of "ENFORCED".

Setting QUERY_REWRITE_INTEGRITY to the value of "STALE_TOLERATED" (i.e. Functionality disabled) will do what you want. Just be sure to understand
the implications of this setting (i.e. DBA becomes responsible for ensuring
that MV is never "stale" for query rewrite).

on 8/26/05 5:17 AM, VIVEK_SHARMA at VIVEK_SHARMA_at_infosys.com wrote:

>
> Thomas, folks
>
> Is there some way of Avoiding creation of Constraint on Source/Master
> table while using Materlialized view refresh?
> NOTE - Production Database does NOT have any constraints & is of 600
GB
>
> Creation of constraints on Source/Master DB Tables would be very time
> consuming, if attempted.
>
> Thanks indeed for the info.
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Thomas Day
> Sent: Thursday, August 25, 2005 10:10 PM
> To: oracle-l_at_freelists.org
> Subject: Re: Some Basic Qs on materialized views
>
> It's not the size of the database that determines how often you
> refresh the materialized views. Rather, it's the frequecy with which
> the data changes on the source database and how important it is to the
> business that that data is reflected in the target database in a
> timely fashion.
>
> We used 15 minutes for transaction oriented tables, 1 hour for tables
> that were less volatile and 24 hours for look-up tables (e.g.,
> country_codes) where the table's content was only changed via a
> software change request.
>
> We used dbms_jobs to schedule and fire the refreshes.
> --
> http://www.freelists.org/webpage/oracle-l
>
> **************** CAUTION - Disclaimer *****************
> This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended
solely
> for the use of the addressee(s). If you are not the intended
recipient, please
> notify the sender by e-mail and delete the original message. Further,
you are
> not to copy, disclose, or distribute this e-mail or its contents to
any other
> person and any such actions are unlawful. This e-mail may contain
viruses.
> Infosys has taken every reasonable precaution to minimize this risk,
but is
> not liable for any damage you may sustain as a result of any virus in
this
> e-mail. You should carry out your own virus checks before opening the
e-mail
> or attachment. Infosys reserves the right to monitor and review the
content of
> all messages sent to or from this e-mail address. Messages sent to or
from
> this e-mail address may be stored on the Infosys e-mail system.
> ***INFOSYS******** End of Disclaimer ********INFOSYS***
> --
> http://www.freelists.org/webpage/oracle-l
>

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l