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?
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';
Dig Deeper on Oracle database backup and recovery
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading