ICX_TRANSACTIONS_U1 ORA-0142

One of the DBAs got this error while doing an E-biz patch through adpatch:

The table is missing the index ICX_TRANSACTIONS_U1
or index ICX_TRANSACTIONS_U1 exists on another table.
Create it with the statement:

Start time for statement below is: Tue Apr 22 2008 18:19:38

CREATE UNIQUE INDEX ICX.ICX_TRANSACTIONS_U1 ON ICX.ICX_TRANSACTIONS
(TRANSACTION_ID) LOGGING STORAGE (INITIAL 4K NEXT 104K MINEXTENTS 1
MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELIST GROUPS 4 FREELISTS 4 ) PCTFREE
10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS PARALLEL TABLESPACE ICXX

AD Worker error:
The following ORACLE error:

ORA-12801: error signaled in parallel query server P000
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found


occurred while executing the SQL statement:

CREATE UNIQUE INDEX ICX.ICX_TRANSACTIONS_U1 ON ICX.ICX_TRANSACTIONS
(TRANSACTION_ID) LOGGING STORAGE (INITIAL 4K NEXT 104K MINEXTENTS 1
MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELIST GROUPS 4 FREELISTS 4 ) PCTFREE
10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS PARALLEL TABLESPACE ICXX

AD Worker error:
Unable to compare or correct tables or indexes or keys
because of the error above


Solution is:

Execute the following SQL to prevent errors during Patch Application through adpatch:
SELECT TRANSACTION_ID, count(*)
FROM ICX.ICX_TRANSACTIONS
GROUP BY TRANSACTION_ID
HAVING count(*)>1
If the Above query returns any Row then Please execute the following SQL :
$ICX_TOP/sql (named ICXDLTMP.sql).

Keep in mind: This program has to be executed at least once a week to clean up ICX_TRANSACTIONS and ICX_SESSION (otherwise they will grow out of control).