When you install Fix Pack 6 or later, you might see errors caused by duplicate user
IDs.
About this task
Fix Pack 6 introduced an index to the user table that prevents creation of
duplicate user IDs. If you upgrade to Fix Pack 6 or later, you might see errors in the
upgrade.log such as: ORA-0145 2: cannot CREATE UNIQUE INDEX; duplicate
keys found
These errors are caused by users with duplicate IDs. If you see such errors, complete the
following steps to resolve the problem.
Procedure
-
Retrieve duplicate user IDs by running the following command:
SELECT userid, countnumber from (SELECT userid, COUNT(userid) AS countnumber FROM users GROUP BY userid) AS my_table WHERE countnumber > 1
- Retrieve the various records for each duplicate user ID by running the following command,
where
id is a user ID retrieved in the previous command.
SELECT uniquekey, type FROM users WHERE userid = id
Two or more records are returned for each user ID. For each user ID, all records
except one have the type inactive.
- For each user ID of type
inactive, change the user ID by using the
following command, where key is the uniquekey
of that user ID.
UPDATE users SET userid = 'NewName' WHERE uniquekey = key and type = 'inactive'
For example: UPDATE users SET userid='MarySmith_12345_inactive' WHERE uniquekey = '12345'
AND type = 'inactive'
Using the 'inactive' clause prevents alteration of an active user in error.
- Apply the index to the user table:
CREATE UNIQUE INDEX USERID_IDX ON
USERS(USERID);