| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Fast refresh materialized view is not updateable
Mike,
What is your Oracle version? There should be no problem. I see you are
refreshing 'scd_financial_summary_pit_mv1'
is trailing "1" a typo or you changed MV name?
I have no problem with your scenari on 8.1.7.2 on Solaris. Although, I
removed "enable query rewrite " - it is not enabled on this instance, but
it shouldn't make a difference.
SQL> connect test/test_at_dev
Connected.
SQL> create table scd_financial_detail(
2 claim_carrier_key varchar2(10),
3 eval_date date,
4 trn_reserve number);
Table created.
SQL> SQL> SQL> create materialized view log on scd_financial_detail2 with rowid (claim_carrier_key, eval_date, trn_reserve) 3 including new values
Materialized view log created.
SQL> create materialized view scd_financial_summary_pit_mv
2 build immediate 3 refresh fast on commit 4 with rowid 5 AS 6 select count(*) n, 7 claim_carrier_key, 8 eval_date, 9 sum(trn_reserve) fin_tot_res, 10 count(trn_reserve) cnt_tot_res 11 from scd_financial_detail 12 group by 13 claim_carrier_key, 14 eval_date
Materialized view created.
SQL> insert into scd_financial_detail values('1', sysdate, 23);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from scd_financial_summary_pit_mv;
N CLAIM_CARR EVAL_DATE FIN_TOT_RES CNT_TOT_RES ---------- ---------- --------- ----------- -----------
1 1 16-APR-02 23 1 SQL> delete from scd_financial_detail;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from scd_financial_summary_pit_mv;
no rows selected
SQL> exec dbms_mview.refresh('scd_financial_summary_pit_mv', 'F');
PL/SQL procedure successfully completed.
SQL>
-----Original Message-----
Sent: Tuesday, April 16, 2002 6:28 PM
To: Multiple recipients of list ORACLE-L
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).
-- 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).Received on Tue Apr 16 2002 - 18:28:19 CDT
 |
 |