I think that the problem is that the column list...
("MANDT", "SYMBOL_ID", "ATZIS", "ASSTYP", "IN_RECNO", "INSTANCE", "VALFR",
"VALTO", "OBJECTTYP", "OBJNR", "STATUS", "CUCOCNT", "ST_VALFR", "ST_VALTO",
"ROOT")
Does not include the 3 ROWID columns you added to the SELECT LIST.
On Mon, Jun 8, 2015 at 3:32 PM, David Barbour <david.barbour1@xxxxxxxxx>
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?
