DAC task create index duplicated error

ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found 

 

Create the index like this and there will be a exception said the index is duplicated:

CREATE UNIQUE INDEX W_AP_XACT_F_U1 ON W_AP_XACT_F

(INTEGRATION_ID ASC,DATASOURCE_NUM_ID ASC) 

 

 Using the sql to find which index is duplicated:

SELECT INTEGRATION_ID ,DATASOURCE_NUM_ID  ,COUNT(*) 

FROM W_AP_XACT_F 

GROUP BY INTEGRATION_ID ,DATASOURCE_NUM_ID HAVING COUNT(*) > 1 

 

If the item is bundred of, and each to duplicated item is same then use sql to delete duplicated , but depned your business rule.

DELETE FROM W_AP_XACT_F where rowid not in(SELECT MIN(rowid)

FROM W_AP_XACT_F GROUP BY INTEGRATION_ID,DATASOURCE_NUM_ID);

 

If the  the two items is not very same , and delete one of them depend on the column then :

 

DELETE FROM W_GL_ACCOUNT_DS where rowid in (SELECT min(rowid)

FROM W_GL_ACCOUNT_DS where GL_ACCOUNT_NUM is null GROUP BY INTEGRATION_ID,DATASOURCE_NUM_ID,SRC_EFF_FROM_DT);

 

 

ora-00054:resource busy and acquire with nowait specified

select t2.username,t2.sid,t2.serial#,t2.logon_time

from v$locked_object t1,v$session t2

where t1.session_id=t2.sid order by t2.logon_time;

 

BIDW150211911-APR-13

 

select sql_text from v$session a,v$sqltext_with_newlines b

 where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value)=b.hash_value

 and a.sid=1502 order by piece;

 

 alter system kill session '1502,119';  --sid, #serial

 

 =================log==================

MESSAGE :::Error while execution : CREATE UNIQUE INDEX 

W_AP_XACT_F_U1 

ON 

W_AP_XACT_F

(

INTEGRATION_ID ASC

,DATASOURCE_NUM_ID ASC

) 

NOLOGGING

 with error DataWarehouse:CREATE UNIQUE INDEX 

W_AP_XACT_F_U1 

ON 

W_AP_XACT_F

(

INTEGRATION_ID ASC

,DATASOURCE_NUM_ID ASC

) 

NOLOGGING

ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found