I'm trying to create materialized view (refresh materialized view whenever there's a commit on base tables) but seeing below error. What could I be missing?
CREATE TABLE item_vp
(
item_num NUMBER,
item_name VARCHAR2(100),
CONSTRAINT item_vp_pk PRIMARY KEY (item_num)
);
CREATE TABLE supp_vp
(
supp_num NUMBER,
supp_name VARCHAR2(100),
supp_id VARCHAR2(100),
CONSTRAINT supp_vp_pk PRIMARY KEY (supp_num)
);
CREATE TABLE item_supp_vp
(
item_supp_num NUMBER,
item_num NUMBER,
supp_num NUMBER,
status VARCHAR2(1),
CONSTRAINT item_supp_vp_pk PRIMARY KEY (item_supp_num),
CONSTRAINT fk_item FOREIGN KEY (item_num) REFERENCES item_vp(item_num),
CONSTRAINT fk_supp FOREIGN KEY (supp_num) REFERENCES supp_vp(supp_num)
);
CREATE MATERIALIZED VIEW LOG ON item_vp WITH ROWID ;
CREATE MATERIALIZED VIEW LOG ON supp_vp WITH ROWID ;
CREATE MATERIALIZED VIEW LOG ON item_supp_vp WITH ROWID ;
--drop MATERIALIZED view item_supp_mv;
CREATE MATERIALIZED VIEW item_supp_mv
REFRESH FAST ON COMMIT
AS
SELECT
item_vp.item_num,
supp_vp.supp_name,
supp_vp.supp_num,
supp_vp.supp_id
FROM
item_vp
JOIN item_supp_vp
ON item_supp_vp.item_num = item_vp.item_num
JOIN supp_vp
ON supp_vp.supp_num = item_supp_vp.supp_num
WHERE
item_supp_vp.status = 'A';
Error report -
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.
I'm using :
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Windows 11 Ent