Skip to Content
Author's profile photo James Zhang

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,

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Wolfe Xue
      Wolfe Xue

      Hi James,

      I met the same issue when import data.


      Export database: Oracle

      Import database: Oracle


      (DB) ERROR: DDL statement failed


      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.



      Author's profile photo James Zhang
      James Zhang
      Blog Post Author

      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.

      Author's profile photo James Zhang
      James Zhang
      Blog Post Author

      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>;