Can I Create Materialized View with REFRESH FAST ON COMMIT & UNION ?

Hi all,

We have requirement to create MV with REFRESH FAST ON COMMIT & UNION. After trying with the scripts below, I get error :

ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

The scripts :
CREATE TABLE TRANS1 (
doc_no VARCHAR2(10) PRIMARY KEY,
docdate DATE,
product VARCHAR2(6),
qty NUMBER(6,2),
doc_type VARCHAR(3)
)

CREATE TABLE TRANS2 (
doc_no VARCHAR2(10) PRIMARY KEY,
docdate DATE,
product VARCHAR2(6),
qty NUMBER(6,2),
doc_type VARCHAR(3)
)

CREATE MATERIALIZED VIEW LOG ON TRANS1
TABLESPACE USERS
WITH PRIMARY KEY CREATE MATERIALIZED VIEW LOG ON TRANS2
TABLESPACE USERS
WITH PRIMARY KEY CREATE MATERIALIZED VIEW MV_TRANS
TABLESPACE users
REFRESH FAST ON COMMIT
WITH PRIMARY KEY
AS
SELECT doc_no, qty FROM TRANS1
UNION ALL
SELECT doc_no, qty FROM TRANS2

Is there any solution to this problem ?

Thank you for your help,
xtanto Received on Mon Feb 18 2008 - 05:19:16 CST