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

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



Leave a Reply