Error code 955 oracle

I need to modify an existing PK. Therefore I drop an recreate it. ALTER TABLE B DROP CONSTRAINT PK_B; ALTER TABLE B ADD CONSTRAINT PK_B PRIMARY KEY ("TYP", "NR", "HH", "QUART"); Unfortunately the...

I need to modify an existing PK. Therefore I drop an recreate it.

ALTER TABLE B DROP CONSTRAINT PK_B;
ALTER TABLE B ADD CONSTRAINT PK_B PRIMARY KEY ("TYP", "NR", "HH", "QUART");

Unfortunately the last Statement will give me an error ORA-00955

If I create the PK constraint like it was defined originally with:

ALTER TABLE B ADD CONSTRAINT PK_B PRIMARY KEY ("TYP", "NR", "HH");

everything works fine.

asked Sep 23, 2014 at 11:04

My-Name-Is's user avatar

1

Perhaps there is an INDEX associated with the PRIMARY KEY CONSTRAINT, and it is also named as PK_B.

You can check it as :

SELECT * FROM USER_INDEXES WHERE TABLE_NAME='<table_name>';

If that’s true, then do :

ALTER INDEX "PK_B" RENAME TO "PK_XYZ";

Update : Regarding ALTER INDEX statement, few important points as mentioned by Justin in the comments

Oracle implicitly creates an UNIQUE index to support the PRIMARY KEY CONSTRAINT. Since, the index is of the same name that of the primary key, and now that the primary key is being modified, it is better to drop and re-create the index again as per the definition of the old primary key.

My conclusion :

  • The primary key constraint is enforced through a unique index.
  • If Oracle already finds an index – unique or non-unique – it uses it
    for the primary key.
  • If the index was initially created as non-unique, it will continue to
    show as non-unique, however it will actually be a unique index.

A good demonstration and quite detailed on other aspects too, by Arup : Primary Keys Guarantee Uniqueness? Think Again.

answered Sep 23, 2014 at 11:12

Lalit Kumar B's user avatar

Lalit Kumar BLalit Kumar B

46.8k12 gold badges93 silver badges123 bronze badges

2

I had the same issue where I had to do the following to delete reference to a table from the view whilst recreating the database from the scratch. I was searching for the same in tables and indexes first.

connect sys/oracle as sysdba;
select * from all_tables
select * from all_indexes
(finally located the reference in the views)
select * from all_views where view_name like '%WKSTSTATE%';
drop view RUEGEN.WKSTSTATE;

answered Aug 5, 2015 at 12:13

donsasikumar's user avatar

Источник

Symptoms

This same error may occur in some situations like:

  1. 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 object

    This error may happen even with an empty database.

  2. 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 object

  3. Some 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)

Cause

This can be caused by either of the following:

  1. The database connection has not been configured properly as outlined in our Connecting JIRA applications to Oracle documentation.

    tip/resting
    Created with Sketch.

    This documentation is version specific, so please ensure the appropriate version is viewed.

  2. 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
  3. 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

Resolution for Cause #1

  1. Stop your JIRA application.
  2. Ensure that the database has been correctly configured, as in our Connecting JIRA applications to Oracle documentation.
  3. Start your JIRA application.

Resolution for Cause #2

Always back up your data before performing any modification to the database. If possible, try your modifications on a test server.

  1. 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>
  2. Stop your JIRA application.
  3. Have your Oracle DBA delete the existing sequence.
  4. 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.
  5. Start your JIRA application.

Resolution for Cause #3

  1. Stop your JIRA application.
  2. 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.
  3. Start JIRA.

Last modified on Nov 14, 2018

Related content

  • No related content found
Источник

Sometimes things just won’t work as expected. You know, programming has its quirks and the like. Normally this error alone would mean you are creating some object with a name that is already in use. But what if you just deleted the previous object and get this error when you try to recreate it?

ERROR at line 1:
ORA-00955: name is already used by an existing object

ORA-00955 in SQL Developer

ORA-00955 in SQL Developer

In Oracle, this if possible if you are deleting a unique constraint. This happens because a Unique Constraint is also an Index. Consider you just deleted the old constraint and now tries to create the updated constraint with the same name. Bang:  ORA-00955.

To fix it you must also delete the index that has the same name as the Unique Constraint. Then you will be able to create the constraint again, that will recreate the index too (with the same name – so much for the “name in use” rule, right?). BTW, the same happens with Primary Keys.

Actually, this is the kind of thing that makes life an adventure. Not always pleasant, but an adventure none the less. Below are some code snippets for your delight. Remember: Oracle is case-sensitive for text comparisons.

--Drop Unique Constraint
alter table mytable drop constraint UC_mytable;
--Drop Index for Unique Constraint
drop index myschema.UC_mytable;
--List all indexes from a table
select * from all_indexes where table_name = 'MYTABLE';
--List all constraints with a specific name
select * from all_constraints where constraint_name = 'UC_mytable';

And now some links for future reference also:
List of some ORA errors: http://docs.oracle.com/cd/A91202_01/901_doc/server.901/a90202/e900.htm

Some folks with the same problem: https://community.oracle.com/message/9907669 and http://stepintooracledba.blogspot.com.br/2013/03/ora-00955-name-is-already-used-by.html

That’s it for today. Cya!

About mauriciorpp

Hi, I am Piccolo — but not the one from Dragon Ball. I’m from a highly competitive and fast-paced world too, the IT industry, and this space will be used to share some challenges I face daily on my career. As you will see, I don’t do brawls but I need to be a fighter! Stay tuned.

This entry was posted in Oracle and tagged Error, Indexes, ORA, Oracle, Scripts, Unique Constraint. Bookmark the permalink.

Источник

Понравилась статья? Поделить с друзьями:
  • Error code 91613 ccleaner
  • Error code 909
  • Error code 905308
  • Error code 903
  • Error code 9003 dead or alive