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

  • by

ORA-00955

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
Connected.
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.

Solutions

The simplest solution is to choose another name for the new schema object. On the other hand, if the name collision surprised you, you can check what type of the existing object it has.

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

PROCEDURE

Oh, it’s a procedure. To me, it looks a little odd and not normal. If you decide to drop the schema object, 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;

DROP PROCEDURE "SH"."EMPLOYEES";

Then execute statements composed above.

SQL> DROP PROCEDURE "SH"."EMPLOYEES";

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 *