Materialized view problem
When I trie to create the following materialized view:
CREATE MATERIALIZED VIEW F_COUNT_TIME_HQ_BBR_NE_PT_SL
TABLESPACE ALT_ADSL_DW_T_FCNT_2
BUILD IMMEDIATE
USING INDEX TABLESPACE ALT_ADSL_DW_I_FCNT_2
REFRESH FAST ON COMMIT WITH PRIMARY KEY
ENABLE QUERY REWRITE
AS
SELECT TIME_KEY, BBRAS_LOCATION || BBRAS_NAME || BBRAS_SLOT BBRAS_NE_KEY,
AVG(IN_DISCARDS) IN_DISCARDS, AVG(OUT_DISCARDS) OUT_DISCARDS,
AVG(IN_OCTETS) IN_OCTETS, AVG(OUT_OCTETS) OUT_OCTETS,
AVG(LAST_CHANGE) LAST_CHANGE, AVG(AVAILABILITY) AVAILABILITY,
COUNT(IN_DISCARDS) CNT_IN_DISCARDS, COUNT(OUT_DISCARDS) CNT_OUT_DISCARDS,
COUNT(IN_OCTETS) CNT_IN_OCTETS, COUNT(OUT_OCTETS) CNT_OUT_OCTETS,
COUNT(LAST_CHANGE) CNT_LAST_CHANGE, COUNT(AVAILABILITY) CNT_AVAILABILITY,
COUNT(*) CNT
FROM F_COUNT_TIME_HQ_BBR_NE_PT, D_BBRAS_NE_PT
WHERE F_COUNT_TIME_HQ_BBR_NE_PT.BBRAS_NE_KEY = D_BBRAS_NE_PT.BBRAS_NE_KEY
GROUP BY BBRAS_LOCATION, BBRAS_NAME, BBRAS_SLOT, TIME_KEY
/
I get the error "ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view".
I have created the materialized view logs for the two tables:
CREATE MATERIALIZED VIEW LOG ON D_BBRAS_NE_PT
TABLESPACE ALT_ADSL_DW_MV_LOG_1
WITH ROWID, SEQUENCE
(BBRAS_NE_KEY, BBRAS_NE_OID, BBRAS_LOCATION, BBRAS_NAME, BBRAS_SLOT, BBRAS_PORT)
INCLUDING NEW VALUES
/
CREATE MATERIALIZED VIEW LOG ON F_COUNT_TIME_HQ_BBR_NE_PT
TABLESPACE ALT_ADSL_DW_MV_LOG_1
WITH ROWID, SEQUENCE
(TIME_KEY, BBRAS_NE_KEY, IN_DISCARDS, OUT_DISCARDS,
IN_OCTETS, OUT_OCTETS, IN_ERRORS, OUT_ERRORS,
LAST_CHANGE, AVAILABILITY)
INCLUDING NEW VALUES
/
I know that is an odd materialized view because the bbras_ne_key column is created with the concatenation of three columns... but is what I need.
If I use only one column, bbras_slot for example, it works fine.
Thanks in advance,
Carlos Dias
|