Re: Problem With Fast Refresh Of Materialized View

The column list in parentheses in the beginning of your CREATE TABLE does not include the three ROWID's.

Either include three ROWID's (rowid1, rowid2, rowid3) in that column list. Or just remove the column list - then columns will be autonamed from the columns/aliases of the query.

Regards

Kim Berg Hansen

http://dspsd.blogspot.com
kibeha_at_gmail.com
_at_kibeha

On Mon, Jun 8, 2015 at 9:32 PM, David Barbour <david.barbour1_at_gmail.com> wrote:

> Oracle 11.2.0.3 RHEL 6.3
>
> Trying to create a materialized view with fast refresh capabilities.
> Created materialized view logs on the base tables with rowid.
>
> CREATE MATERIALIZED VIEW "SAPR3"."V_IBINVALUES_F"
> ("MANDT", "SYMBOL_ID", "ATZIS", "ASSTYP", "IN_RECNO", "INSTANCE", "VALFR",
> "VALTO", "OBJECTTYP", "OBJNR", "STATUS", "CUCOCNT", "ST_VALFR", "ST_VALTO",
> "ROOT")
> TABLESPACE PSAPBTABD
> BUILD IMMEDIATE
> AS
> SELECT T1."MANDT", T1."SYMBOL_ID", T1."ATZIS", T1."ASSTYP", T1."IN_RECNO",
> T2."INSTANCE", T2."VALFR", T2."VALTO", T2."OBJECTTYP", T2."OBJNR",
> T2."CSTATUS", T2."CUCOCNT",
> T3."VALFR", T3."VALTO", T3."ROOT"
> FROM "IBINVALUES" T1, "IBIN" T2, "IBST" T3
> WHERE T2."MANDT" = T1."MANDT"
> AND T2."IN_RECNO" = T1."IN_RECNO"
> AND T3."MANDT" = T1."MANDT"
> AND T3."INSTANCE" = T2."INSTANCE"
> AND ( T1."ASSTYP" = ' ' OR T1."ASSTYP" = 'F' )
> /
> Materialized view created.
>
> Then check the requirements against mv_capabilities:
>
>
> SQL> exec dbms_mview.explain_mview(mv=>'V_IBINVALUES_F',stmt_id=>'100');
>
> PL/SQL procedure successfully completed.
>
> SQL> commit;
>
> Commit complete.
>
> 1 select CAPABILITY_NAME, POSSIBLE, MSGTXT, RELATED_TEXT from
> mv_capabilities_table
> 2 where capability_name like 'REFRESH%'
> 3* and capability_name not like '%PCT%'
> SQL> /
>
> CAPABILITY_NAME P MSGTXT
> RELATED_TEXT
> ------------------------------ -
> ----------------------------------------------------------------------
> ----------------------------------------
> REFRESH_COMPLETE Y
> REFRESH_FAST N
> REFRESH_FAST_AFTER_INSERT N the SELECT list does not have the rowids
> of all the detail tables T3
> REFRESH_FAST_AFTER_ONETAB_DML N see the reason why
> REFRESH_FAST_AFTER_INSERT is disabled
> REFRESH_FAST_AFTER_ANY_DML N see the reason why
> REFRESH_FAST_AFTER_ONETAB_DML is disabled
>
> Okay - so I include the rowids - or at least I try:
>
> SQL> drop materialized view V_IBINVALUES_F;
>
> Materialized view dropped.
>
> SQL> CREATE MATERIALIZED VIEW "SAPR3"."V_IBINVALUES_F"
> ("MANDT", "SYMBOL_ID", "ATZIS", "ASSTYP", "IN_RECNO", "INSTANCE", "VALFR",
> "VALTO", "OBJECTTYP", "OBJNR", "STATUS", "CUCOCNT", "ST_VALFR", "ST_VALTO",
> "ROOT")
> 2 3 TABLESPACE PSAPBTABD
> 4 BUILD IMMEDIATE
> 5 AS
> 6 SELECT
> 7 T1.ROWID, T2.ROWID, T3.ROWID,
> 8 T1."MANDT", T1."SYMBOL_ID", T1."ATZIS", T1."ASSTYP", T1."IN_RECNO",
> 9 T2."INSTANCE", T2."VALFR", T2."VALTO", T2."OBJECTTYP", T2."OBJNR",
> T2."CSTATUS", T2."CUCOCNT",
> 10 T3."VALFR", T3."VALTO", T3."ROOT"
> FROM "IBINVALUES" T1, "IBIN" T2, "IBST" T3
> 11 12 WHERE T2."MANDT" = T1."MANDT"
> 13 AND T2."IN_RECNO" = T1."IN_RECNO"
> 14 AND T3."MANDT" = T1."MANDT"
> 15 AND T3."INSTANCE" = T2."INSTANCE"
> 16 AND ( T1."ASSTYP" = ' ' OR T1."ASSTYP" = 'F' )
> 17 /
> T2."INSTANCE", T2."VALFR", T2."VALTO", T2."OBJECTTYP", T2."OBJNR",
> T2."CSTATUS", T2."CUCOCNT",
>
> *
> ERROR at line 9:
> ORA-01730: invalid number of column names specified
>
> The little asterisk is under the T2.CUCOCNT selection.
>
> It's Monday. What am I missing here?
>

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