Ошибка 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 existing synonym. For table, view, sequence, or private synonym, we can directly use RENAME to make it.

SQL> rename TAB to TAB2;

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. I have talked about it in the post: How to Rename Stored Procedure in Oracle.

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');

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.

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

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;

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.

ORA-00955: name is already used by an existing object error occurs while creating a table, view, index, sequence, synonym, types, constraints or other object with a name that already exists in the database. A database object with the same name already exists. It is not possible to create another database object with the same name in Oracle. If you try to create a database object with the same name, you will get the error ORA-00955: name is already used by an existing object.

In the Oracle database, database objects are uniquely identifiable. If you try to create a table, view, sequence, index, or other object having the same name as an existing database item, Oracle will reject your request. The error ORA-00955: name is already used by an existing object will be sent by Oracle because it detected a database object with the same name.

The Problem

Oracle will not be able to create a table, view, sequence, synonyms, or other object having the same name as another object that already exists in the database. If Oracle discovers another database object with the identical name, it will throw an exception.

CREATE TABLE EMP(
    id NUMBER PRIMARY KEY,
    NAME VARCHAR2(255) NOT NULL
);

CREATE TABLE EMP(
    id NUMBER PRIMARY KEY,
    NAME VARCHAR2(255) NOT NULL,
    salary numeric(10,2)
);

Exception

Error report -
ORA-00955: name is already used by an existing object
00955. 00000 -  "name is already used by an existing object"
*Cause:    
*Action:

Solution 1

You’re attempting to create a table, view, or other object with the same name as another. It’s possible that you duplicated the code and tried to run it without altering the name. It’s possible that you haven’t noticed the database object names that have previously been created. You attempt to create with a generic name that is already in use in the database for a database object. This error will be resolved if you create a database object with a unique name, such as a table, view, sequence, index, synonyms, or other object.

CREATE TABLE EMP(
    id NUMBER PRIMARY KEY,
    NAME VARCHAR2(255) NOT NULL
);

CREATE TABLE MANAGER(
    id NUMBER PRIMARY KEY,
    NAME VARCHAR2(255) NOT NULL,
    salary numeric(10,2)
);

Solution 2

You might need to remove an existing database object and replace it with a new one. The error will be thrown if you try to create database objects like as tables, views, sequences, indexes, synonyms, or other objects without deleting them from the database. The old object should be removed, and a new one should be created in its place.

DROP TABLE EMP;

CREATE TABLE EMP(
    id NUMBER PRIMARY KEY,
    NAME VARCHAR2(255) NOT NULL,
    salary numeric(10,2)
);

Solution 3

The CREATE OR REPLACE option in Oracle allows you to replace an existing item. In this scenario, the existing object will be replaced.

CREATE OR REPLACE TABLE EMP(
    id NUMBER PRIMARY KEY,
    NAME VARCHAR2(255) NOT NULL,
    salary numeric(10,2)
);

Solution 4

You attempt to create a database object such as a table, view, index, sequence, synonyms, or other object in a schema that is incorrect to create. The database object name should be prefixed with the schema name in this case. Before creating a database object, think about the schema name.

CREATE OR REPLACE TABLE <schema_name>.EMP(
    id NUMBER PRIMARY KEY,
    NAME VARCHAR2(255) NOT NULL,
    salary numeric(10,2)
);

CREATE OR REPLACE TABLE hr.EMP(
    id NUMBER PRIMARY KEY,
    NAME VARCHAR2(255) NOT NULL,
    salary numeric(10,2)
);

Solution 5

If the error ORA-00955: name is already used by an existing object occurs while creating objects in the database, such as tables, views, indexes, sequences, synonyms, and so on, execute the query below to locate any existing objects in the database. The query below will return information about the database object, such as its name and schema. You can locate the problem in the database code and make the necessary changes.

SELECT * FROM all_objects WHERE object_name = 'EMP';