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@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_detail
2 with rowid (claim_carrier_key, eval_date, trn_reserve)
3 including new values
4 ;
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
15 /
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 <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>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: [EMAIL PROTECTED]
>
>
>-----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: [EMAIL PROTECTED]
>
>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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
>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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
