Recently I had a case of duplicate rows in a table with Primary Key enabled.
More common case is to catch CBO (Cost Based Optimizer) to return a wrong results, mainly due to sophisticated nature of CBO engine (CBO is the most complex part of the Oracle database), the complex SQLs you issue, and the related SQL transformations that the CBO needs to go through.
While such kind of bugs are relatively easy to resolve, duplicate rows on PK are not.
The best you can do is to troubleshoot the issue to narrow down the problem to smaller subsystem that can help Oracle Support to speed up the bug resolution.
First I checked the index to see if it is enabled and validated.
I tried to rebuild the PK index and to re-validate the PK constraint.
All those actions passed fine.
Next I checked for duplicate values in the PK index.
To do that, I’ve run the following SQL:
select /*+ index(table_name, pk) */ * from table_name where id = 12345; 1 rows returned
Since the query returned only 1 row, PK constraint and index underneath are both fine.
That explains why the PK index can be in ENABLED & VALIDATED state and why I can rebuild the index or re-validate PK constraint.
To check the consistency of the table you can run the following SQL:
select /*+ full(table_name) */ * from table_name where id = 12345; 2 rows returned
While the PK constraint is fine, in table itself there are 2 rows that share the same value for PK.
When I was researching what was going on in a recent days, I was able to collect all pieces together.
The reason for having duplicate index is combination of bug during the data import (bug related to impdp job), the impdp options used during schema import and the database version.
In such cases where the bug is obvious, having an AHF (Autonomous health Check framework) in place is worth as a gold.