2

I would like to create a materialized view that should build immediate and refresh fast on every commit in the database for the below inner join query.

Sample Query format:

CREATE MATERIALIZED VIEW test_view BUILD IMMEDIATE REFRESH FAST ON COMMIT AS
  SELECT
    INTTABLE.COLUMN1 AS "init col",
    TABLE1.COLUMN1   AS "tab1 col",
    TABLE2.COLUMN1   AS "tab2 col",
    TABLE3.COLUMN1   AS "tab3 col"
  FROM TABLE1
    INNER JOIN TABLE3
      ON TABLE1.COLUMN1 = TABLE3.COLUMN2
    INNER JOIN TABLE2
      ON TABLE2.COLUMN1 = TABLE3.COLUMN3
    INNER JOIN INTTABLE
      ON INTTABLE.COLUMN1 = TABLE2.COLUMN2;

Error Message:

Error at Command Line : 11 Column : 12
Error report -
SQL Error: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
12054. 00000 - "cannot set the ON COMMIT refresh attribute for the materialized view"
*Cause: The materialized view did not satisfy conditions for refresh at
commit time.
*Action: Specify only valid options.
Makoto
  • 104,088
  • 27
  • 192
  • 230
Arun Kumar Shanmugam
  • 21
  • 1
  • 1
    I I think you have the same problem as described in this question [link](http://stackoverflow.com/questions/7505020/oracle-how-to-create-a-materialized-view-with-fast-refresh-and-joins). – Mikhailov Valentin Jan 01 '16 at 05:46

0 Answers0