Hi Folks,
When doing system copy import for oracle database, sometime we are hitting error "
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found".
The description of
ORA-01452 clearly tells us there are duplicate keys in the table, you cannot create unique index. As the name "unique index" means the index cannot include duplicate keys, if we have duplicate keys in the table, then we cannot create unique index on the corresponding columns, thus error ORA-01452 occurs.
When this error
ORA-01452 occurs, we know below:
1. Target system:
table to be created unique index has duplicate keys
2. Source system:
2.1 the table content should be the same with target system (this is what system copy does!), so there should be duplicate keys on source table already!
2.2 the unique index already in the table (that is why import is trying to create unique index on target system)
We already have unique index on source table, how this could happen that source system has duplicate keys?
-Answer: Probably the index has corruption, which does not do the unique check correctly.
Then we need to check below to double confirm this assumption:
1. count the entries via table full scan
SELECT /*+ full(<table_name>) */ COUNT(*) FROM <schema>.<table_name>;
2. count the entries via unique index
SELECT /*+ index(<table_name> <unique index name>) */ COUNT(*) FROM <schema>.<table_name> where index_column is not null;
If the two count results are different, then we can perform the corruption check:
analyze table <schema>.<table_name> validate structure cascade online;
If indeed, the index has corruption, we cannot simply solve it via rebuilding. We have to follow up SAP note 23237 and 11369 to remove the duplicates first. Please note the application consultant who owns the table should be responsible for which entries can be removed, which cannot removed. DO NOT REMOVE ANY ENTRIES IF YOU ARE NOT SURE. Afterwards we can recreate the unique index on source system.
Above can be a guideline to think about this problem, because there could be TINY possibility that system copy-import insert the same line one more times.
Reference SAP notes:
#11369 - ORA-1452 during import after export from other system
#23237 - duplicate keys, duplicate rows, duplicate records
#1413928 - Index corruption/wrong results after rebuild index ONLINE
Best regards,
James
