Problem With Fast Refresh Of Materialized View

From: David Barbour <david.barbour1_at_gmail.com>
Date: Mon, 8 Jun 2015 14:32:24 -0500
Message-ID: <CAFH+iffa8K4zp_u8CmTDk1Nv0YNucOUz2RZPUva0pVNw8cizTA_at_mail.gmail.com>

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

Received on Mon Jun 08 2015 - 21:32:24 CEST