Oracle: Error ORA-00955 name is already used by an existing object

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!

Advertisements

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 , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s