Q
Problem solve Get help with specific problems with your technologies, process and projects.

ORA-1452 error after export/import

I'm exporting an Oracle DB between two servers (Intel platform) in order to change HW to improve performance. I did a full export dump with no problem and after that I did an import on new HW. Import was finished successfully with warning; I received seven errors: "ORA-1452: cannot create unique index: duplicate key found."

Hi Brian. I'm exporting an Oracle DB between two servers (Intel platform) in order to change HW to improve performance. I'm using Oracle release 9.2.0.2. This is a database for BW SAP. I did a full export dump with no problem and after that I did an import on new HW.

Import was finished successfully with warning; I received seven errors: "ORA-1452: cannot create unique index: duplicate key found."

It seems to be that the export utility has some problems exporting data from these seven tables. Looking in these tables in the source DB I can't find duplicate records. I tried to create a table as a select of one of these tables; the table was created successfully with index too.

This is the key of one of the tables:

REQUEST       NOT NULL VARCHAR2(30)

DATAPAKID     NOT NULL VARCHAR2(6)

PARTNO        NOT NULL NUMBER(10)

RECORD        NOT NULL NUMBER(10)
Could you help me?
It is odd that you have duplicate records. You will have to remove the duplicate records in order to be able to create a UNIQUE index or a PRIMARY KEY or UNIQUE constraint. The first thing to do is to determine the records that have duplicate values. Let's assume I have a table defined as follows:
CREATE TABLE test (FIRST varchar2(20),LAST varchar2(20));
I now want to create a UNIQUE index on the columns FIRST and LAST, but I get the ORA-1452 errors. So how do I identify which rows are duplicates? I can use a SQL statement similar to the following:
SELECT t1.ROWID,t1.first,t1.last
FROM test t1,
   (SELECT t2.first,t2.last
    FROM test t2
    GROUP BY t2.first,t2.last
    HAVING count(*) > 2) x
WHERE t1.first = x.first AND t1.last=x.last;
The above query uses an inline view to find all combinations of FIRST and LAST that have more than one record in the table. Using those combinations, I get the values along with the row's identifier. Now that I know the ROWID, I can remove one of the duplicates with a simple DELETE statement as follows:
DELETE FROM test WHERE rowid='abca.......d';
This was last published in January 2006

Dig Deeper on Oracle database backup and recovery

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close