Materialized View Refresh Error

ORA-12014 error occurs when you or your scheduled job is trying to refresh a Materialized View. You will see this whenever the definition of Materialized View does not match with the base table. What does that mean? The M-View was created with Primary Key but the base table lacks a Primary Key constraints. Now, we have a  problem, the PK constraint is missing from the base table. You might be wondering, how does Oracle lets you create M-View when the M-view definition does not match with the table?
Someone might have deleted the PK after the M-View was created or it will let you create but not let you refresh it. I will leave that up-to you to figure it out. Anyways, let's proceed and see how we can replicate and fix it.

Refresh the Materialized View.

BEGIN
 DBMS_MVIEW.REFRESH('QA_ADMIN.MY_REVIEW_TAB','C');
END;

Refresh Error:

Error starting at line 12 in command:
BEGIN
 DBMS_MVIEW.REFRESH('QA_ADMIN.MY_REVIEW_TAB','C');
END;
Error report:
ORA-12018: following error encountered during code generation for "WEB_ADMIN"."MY_REVIEW_TAB"
ORA-12014: table 'MY_REVIEW_TAB' does not contain a primary key constraint
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2566
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2779
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2748
ORA-06512: at line 2
12018. 0000 -  "following error encountered during code generation for \"%s\".\"%s\""
*Cause:    The refresh operations for the indicated materialized view could
           not be regenerated due to errors.
*Action:   Correct the problem indicated in the following error messages and
           repeat the operation.
Error starting at line 12 in command:

If you read the error message carefully, Oracle has identified the issue. The base table does not contain a primary key constraint. M-View is using PK while refreshing when the PK is missing from the base table. Sometime Oracle error messages are too vague and does not point you to the right directions but that does not mean you should stop reading it. I always read it and look for a clue and have helped me at troubleshooting.

Checking the Definition of  Materialized View's base table.

Describe my_reiew_tab;
Base Table Describe:
Name        Null Type      
----------- ---- -------------
SITE               VARCHAR2(200)
LOC_DEST           VARCHAR2(50)
ID                 NUMBER

Materialized View DDL

CREATE materialized VIEW "WEB_ADMIN"."QA_ADMIN.MY_REVIEW_TAB" ("ID", "SITE", "LOC_DEST") WITH PRIMARY KEY USING DEFAULT local ROLLBACK SEGMENT USING enforced CONSTRAINTS DISABLE query rewrite AS
SELECT id, 
       site, 
       loc_dist 
FROM   my_review_tab@my_db_link;

We know the issue; the primary key is missing on base table or the Materialized View defination is using a PK which don't exist. Based on your business need, you can apply solution 1 or 2:

Solution -1
Adding a Primary key to a base table.

ALTER TABLE qa_admin.my_review_tab 
  ADD PRIMARY KEY (id); 

Solution -2
Drop and recreate definition of M-view using ROWID instead of Primary Key

DROP materialized VIEW web_admin"."qa_admin.my_review_tab;

CREATE materialized VIEW "WEB_ADMIN"."QA_ADMIN.MY_REVIEW_TAB" ("ID", "SITE", "LOC_DEST") with ROWID USING DEFAULT local ROLLBACK SEGMENT USING enforced CONSTRAINTS DISABLE query rewrite AS
SELECT id, 
       site, 
       loc_dist 
FROM   my_review_tab@my_db_link;

If you check the definition of M-view before, it was built using Primary Key, here we changed that to use RowID instead. Either will fix your issue, try running the refresh now!