Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    7

    Red face Unanswered: Materialized view problem creation in Oracle9i

    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.

  2. #2
    Join Date
    Dec 2003
    Posts
    10

    Concat function

    Hi cdias,

    Why don't u use concat function

    SELECT TIME_KEY,
    concat(concat(BBRAS_LOCATION,BBRAS_NAME), BBRAS_SLOT) BBRAS_NE_KEY,

    I'm not so sure whether it work, but anyway just try it...

    it works fine for complete refresh.

  3. #3
    Join Date
    Dec 2003
    Posts
    7
    Thanks for your tip... but it doesn't work with fast refresh...

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    How about:
    REFRESH COMPLETE ON COMMIT


    this should do what you want.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •