Ok, you are trying to create a materialized view involving a database link and found a ORA-12014, right?
CREATE MATERIALIZED VIEW &OWNER..MVW_NAME REFRESH FORCE ON DEMAND AS SELECT COL1, COL2, COL3 FROM TABLE@REMOTE_DB; * ERROR at line 1: ORA-12014: table 'TABLE' does not contain a primary key constraint SQL>
It blowed me sometime ago. But it’s not complicated to workaround it, just try to:
CREATE MATERIALIZED VIEW &OWNER..MVW_NAME REFRESH FORCE ON DEMAND AS select * from (SELECT COL1, COL2, COL3 FROM TABLE@REMOTE_DB);
An alternative is to use MV log + WITH ROWID on REMOTE_DB side:
CREATE MATERIALIZED VIEW LOG MVW_LOG_NAME ON TABLE WITH ROWID;
And
CREATE MATERIALIZED VIEW &OWNER..MVW_NAME REFRESH FORCE ON DEMAND WITH ROWID AS SELECT COL1, COL2, COL3 FROM MVW_LOG_NAME@REMOTE_DB;
PS: Make sure username used in remote_db database link has select privileges on MV log. On source db issue:
SELECT LOG_TABLE FROM DBA_MVIEW_LOGS WHERE LOG_OWNER='OWNER' AND MASTER = 'TABLE';
This will give you MV log table name. On target side issue:
SELECT * FROM MVW_LOG_NAME@remote_db;
See ya!
Matheus.