MarkView All Components - ORA-00955: name is already used by an existing object during patchset application

Applies To

• ERP System: Oracle, SAP
• MarkView Version: All versions running on Oracle 10g

Behavior

• 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

Known Causes

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.

Resolution

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.

1. SQL> drop index SF_AUA_ALT_USER_ASSIGN_PK;
2. SQL> ALTER TABLE SF_ALTERNATE_USER_ASSIGNMENT
ADD CONSTRAINT SF_AUA_ALT_USER_ASSIGN_PK
PRIMARY KEY (alternate_user_assignment_id)
USING INDEX
STORAGE(
INITIAL 1M
NEXT 1M
PCTINCREASE 0)
TABLESPACE MVX;

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.