Question: I am creating a materialized view
using the ROWID option and I get the ORA-12014 error:
ORA-12014: table 'MYTAB' does not contain a
primary key constraint
Why should it if I try to create a MV with WITH ROWID refresh
option and not WITH PRIMARY KEY one?
Answer:
The oerr utility notes this on the ORA-12014 error:
ORA-12014: table 'string'
does not contain a primary key constraint
Cause:
The CREATE MATERIALIZED VIEW LOG command was issued with the WITH
PRIMARY KEY option and the master table did not contain a primary
key constraint or the constraint was disabled.
Action:
Reissue the command using only the WITH ROWID option, create a
primary key constraint on the master table, or enable an existing
primary key constraint.
You get the ORA-12014 error because Oracle cannot guarantee that
a ROWID never changes.
ROWIDs may change when a table is reorganized or when
row
movement is enabled.
Also, without a primary key, you cannot do a materialized view fast
refresh.
Oracle must have a primary key defined because Oracle has no
mechanism to update a materialized view replication when a ROWID
changes.
See these best
practices for defining a materialized view.
