How to Resolve ORA-00955: name is already used by an existing object

  • by


ORA-00955 means that the name of the schema object that you tried to create is already used and taken by some other existing schema object. The is because all schema objects share the same namespace within a schema.

At the moment, you can either choose a different name for the new object or drop the old same name object. If you decide to drop the existing object, you have to know what object type it has in order to compose the statement.

Let's see the following example.

SQL> conn sh/sh
SQL> set head off;
SQL> create synonym employees for hr.employees;
create synonym employees for hr.employees
ERROR at line 1:
ORA-00955: name is already used by an existing object

We tried to create a synonym for a table, but it failed with ORA-00955.


The simplest solution is to choose another name for the new synonym, but you had better to memorize what is the based object of the new synonym. In practice, we use the same name as the base object for a new synonym.

On the other hand, if the name collision surprised you, you'd better to check what type of the existing object it has.

SQL> select object_type from all_objects where owner = 'SH' and object_name in ('EMPLOYEES');


In this case, it's a procedure. To me, it looks a little odd and not normal. If you decide to drop the schema object for solving ORA-00955, you can just issue your DROP statement or compose DROP statement like the following:

SQL> select 'DROP ' || object_type || ' "' || owner || '"."' || object_name || '";' from all_objects where owner = 'SH' and object_type <> 'PACKAGE BODY' and object_name in ('EMPLOYEES') order by owner, object_type, object_name;


Then execute statements composed above.


Procedure dropped.

I know you may not have to compose DROP statement like mine. In my case, I had to drop a lot of same name and existing objects which have different object types for installing an application later. That's why I introduce the way to do it efficiently.

Now we can create the new object with the same name again.

SQL> create synonym employees for hr.employees;

Synonym created.

We did it, no ORA-00955 means no name collision.

Next, we should consider a problem that may occur when you start to use the synonym.

SQL> select * from employees;
select * from employees
ERROR at line 1:
ORA-00942: table or view does not exist

Basically, ORA-00942 is an object privilege problem. You may take some times to look at it.

Leave a Reply

Your email address will not be published. Required fields are marked *