Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

vpolasa5 days ago — edited 4 days ago

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

Comments
Post Details
Added 5 days ago
2 comments
64 views