System copy-import fails with error ORA-01452
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
Hi James,
I met the same issue when import data.
BG:
Export database: Oracle 10.2.0.1
Import database: Oracle 11.2.0.4
Error"
(DB) ERROR: DDL statement failed
(CREATE UNIQUE INDEX "CSMCLSMAP~0" ON "CSMCLSMAP" ( "ASC_CLASS", "L_CLASS_1", "L_CLASS_2" ) TABLESPACE PSAPSR3 STORAGE (INITIAL 0000000016K NEXT 0000000040K MINEXTENTS 0000000001 MAXEXTENTS 2147483645 PCTINCREASE 0 ) NOLOGGING PARALLEL 4 )
DbSlExecute: rc = 99
(SQL error 12801)
error message returned by DbSl:
ORA-12801: error signaled in parallel query server P003
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
(DB) INFO: disconnected from DB
"
I try your method, But didn't work. Any idea about it.
BR
Wolfe
What is the check result on the source table?
As mentioned, another possible reason could be sapinst does the duplicate insert into the corresponding table. If there is no corruption.
If it it is unique index corruption, we may need to use following query:
SAP note 23237 query needs to adjusted:
select alldata.rowid, alldata.*
from <table> alldata,
(SELECT /*+ full(<table>) */ <keycolumn 1>, ..., <keycolumn n>
FROM <table>
GROUP BY <keycolumn 1>, ..., <keycolumn n>
HAVING COUNT(*) > 1) keydata
where alldata.<keycolumn 1>=keydata.<keycolumn 1>
and ...
and alldata.<keycolumn n>=keydata.<keycolumn n>;