ORA-00955: Name is already used by an existing object
症状
This same error may occur in some situations like:
When installing or upgrading JIRA applications with Oracle, error messages like the following appear in the logs:
2008-02-27 13:56:54,083 Thread-1 [core.entity.jdbc.DatabaseUtil] SQL Exception while executing the following: CREATE TABLE nexjira_prod.changegroup (ID NUMBER(18,0) NOT NULL, issueid NUMBER(18,0), AUTHOR VARCHAR2(255), CREATED DATE, CONSTRAINT PK_changegroup PRIMARY KEY (ID)) Error was: java.sql.SQLException: ORA-00955: name is already used by an existing objectThis error may happen even with an empty database.
Upgrading GreenHopper will throw the below exception caused by the same problem:
Exception: com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library: Database: - name:Oracle - version:Oracle Database 11g Release 11.1.0.7.0 - 64bit Production - minor version:1 - major version:11 Driver: - name:Oracle JDBC driver - version:11.1.0.7.0-Production java.sql.SQLSyntaxErrorException: ORA-00955: name is already used by an existing objectSome JIRA applications operations such as creating or cloning issues will fail, throwing the below exception:
2012-10-16 10:53:00,156 http-80-4 ERROR xxxx 652x52361x3 1hm20ke 12.186.168.242,198.63.100.56 /browse/IBU-84 [net.java.ao.sql] Exception executing SQL update <CREATE SEQUENCE "AO_544E33_KEY_ID_SEQ" INCREMENT BY 1 START WITH 1 NOMAXVALUE MINVALUE 1> java.sql.SQLSyntaxErrorException: ORA-00955: name is already used by an existing object at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91) at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413) at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034) at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183) at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:942) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222) at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1706)
原因
This can be caused by either of the following:
The database connection has not been configured properly as outlined in our Connecting JIRA applications to Oracle documentation.
This documentation is version specific, so please ensure the appropriate version is viewed.
- The database already has a pre-existing sequence in it which needs to be removed, which often occurs in Active Objects tables. There is an open issue to track automated detection of this: JRA-29155 - Getting issue details... STATUS
- The database driver of version 12.1.0.2.0 is currently bugged and may throw the error as well. The following open bug tracks this behaviour: JRA-60007 - Getting issue details... STATUS .
ソリューション
Resolution for Cause #1
- Stop your JIRA application.
- Ensure that the database has been correctly configured, as in our Connecting JIRA applications to Oracle documentation.
- Start your JIRA application.
Resolution for Cause #2
データベースの変更を行う場合は 必ず事前にバックアップを取得してください。可能な場合はテスト サーバーで変更を試すことをおすすめします。
Identify the sequence that is causing the error - this can be done by reviewing the error in the logs, it will display an error such as the below
<CREATE SEQUENCE "AO_60DB71_ISSUERANKING_ID_SEQ" INCREMENT BY 1 START WITH 1 NOMAXVALUE MINVALUE 1>- Stop your JIRA application.
- Have your Oracle DBA delete the existing sequence.
- There may be more than one Oracle sequence causing errors. Using the steps above, locate all sequences causing errors within the log files and remove them.
- Start your JIRA application.
Resolution for Cause #3
- Stop your JIRA application.
- Install the 12.1.0.1.0 Oracle driver instead (it can be downloaded from http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html). To install it, copy the driver to your
JIRA_INSTALL/lib/folder and remove the current 12.1.0.2.0 driver. - Jira を起動します。