Yep - everybody immediately picked up on it.
Thanks.
On Mon, Jun 8, 2015 at 2:45 PM, Kim Berg Hansen <kibeha@xxxxxxxxx> wrote:
Hi David
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@xxxxxxxxx
@kibeha
On Mon, Jun 8, 2015 at 9: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?
