RE: Fast refresh materialized view is not updateable

From: Mike Killough <mwkillough_at_hotmail.com>
Date: Tue, 16 Apr 2002 14:28:27 -0800
Message-ID: <F001.00445E12.20020416142827@fatcity.com>

Vadim,

I added the field but it still won't fast refresh. When it is first created, the STALENESS col in dba_mviews = FRESH. When I delete a record in the base table, it becomes UNUSABLE. A fast refresh gives me this error:

QUESTT:hcl_data> exec
dbms_mview.refresh('scd_financial_summary_pit_mv1','F'); BEGIN dbms_mview.refresh('scd_financial_summary_pit_mv1','F'); END;

*
ERROR at line 1:
ORA-12057: materialized view "HCL_DATA"."SCD_FINANCIAL_SUMMARY_PIT_MV1" is INVALID and must complete refresh

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 814
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 872
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 852
ORA-06512: at line 1

Mike

>From: Vadim Gorbounov <vgorbounov_at_724.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: Fast refresh materialized view is not updateable
>Date: Tue, 16 Apr 2002 13:53:29 -0800
>
> Mike,
> You missing this field:
>
> count(trn_reserve) cnt_tot_res
>
> Cheers,
>
>Vadim Gorbounov
>Oracle DBA
>724 Solutions Inc.
>Tel:(416)226-2900 ext 5070
>Email: vgorbounov_at_724.com
>
>
>-----Original Message-----
>Sent: Tuesday, April 16, 2002 4:14 PM
>To: Multiple recipients of list ORACLE-L
>
>
>I create a fast refresh materialized view that is a single table
>aggregation, but dml on the base table does not update the materialized
>view. In dba_mviews, FAST_REFRESHABLE = DIRLOAD_LIMITEDDML. I think that I
>have satisfied the requirements for a fast refresh MV. I can't see the
>problem. Here is a stripped down version of the view, that can be created,
>but is not updateable:
>
>create materialized view log on scd_financial_detail
>tablespace &&tbsname
>with rowid (claim_carrier_key, eval_date, trn_reserve)
>including new values
>;
>
>create materialized view scd_financial_summary_pit_mv
> tablespace &&tbsname
> build immediate
> refresh fast on commit
> with rowid
> enable query rewrite AS
> select count(*),
> claim_carrier_key,
> eval_date,
> sum(trn_reserve) fin_tot_res
> from scd_financial_detail
> group by
> claim_carrier_key,
> eval_date
>/
>
>Could anyone please tell me what I'm missing?
>
>Thanks,
>
>Mike
>
>
>_________________________________________________________________
>MSN Photos is the easiest way to share and print your photos:
>http://photos.msn.com/support/worldwide.aspx
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Mike Killough
> INET: mwkillough_at_hotmail.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>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.com
>--
>Author: Vadim Gorbounov
> INET: vgorbounov_at_724.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>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).

---

MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mike Killough
  INET: mwkillough_at_hotmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).

Received on Tue Apr 16 2002 - 17:28:27 CDT