- ERP System: Oracle, SAP
- MarkView Version: All versions running on Oracle 10g
- While applying a patchset (e.g. the 5.10.9 patchset), the following error is encountered:
ALTER TABLE SF_ALTERNATE_USER_ASSIGNMENT * ERROR at line 1: ORA-00955: name is already used by an existing object
The issue described here is a known issue with the Oracle 10g Database. As detailed in Metalink Note 309821.1, when a constraint is dropped in an Oracle 10g instance, Oracle will not drop the associated index.
When the MarkView database objects upgrade is run on a 10g instance, there may be occurrences of this error. Within a few of the scripts, constraints are dropped without the associated indexes being dropped separately, and an attempt is subsequently made to add that same index. On Oracle 9i instances this is not an issue because the associated index will be dropped when the constraint is dropped.
To workaround this error, it should be possible to execute the following statements below, which will manually drop the index, then re-create the Constraint on the correct table/column.
SQL> drop index SF_AUA_ALT_USER_ASSIGN_PK;
SQL> ALTER TABLE SF_ALTERNATE_USER_ASSIGNMENT
ADD CONSTRAINT SF_AUA_ALT_USER_ASSIGN_PK
PRIMARY KEY (alternate_user_assignment_id)
Once these statements have executed successfully, the Verify script can be run to ensure that there are no differences between your environment and a standard environment.
Keywords: 00955, ORA, ORA-00955, 5.10.9, patchset, contraint, index