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 the original object type is in order to compose the statement.

Even though you intent to use CREATE OR REPLACE to replace the old one, but it still failed with ORA-00955.

SQL> CREATE OR REPLACE FUNCTION CUST_SRV (CUST_NO VARCHAR2)
...
CREATE OR REPLACE FUNCTION CUST_SRV (CUST_NO VARCHAR2)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

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

1. Rename the Object

The simplest solution is to choose another name for the new synonym. For table, view, sequence, or private synonym, we can directly use RENAME to make it.

SQL> rename TAB to TAB2;

Table renamed.

Only 4 object types can use RENAME clause to change their name like this:

  • Table
  • View
  • Sequence
  • Private synonym

For stored procedures, you have to drop it then create it so as to rename it.

2. Drop the Object

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

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

PROCEDURE

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;

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 *