Ora 00955 Name Is Already Used by an Existing Object: Causes and Fixes

Ora 00955 Name Is Already Used By An Existing Object: Causes And Fixes

Ora 00955 identify is already used by an present object error happens while you attempt to create a brand new object with a reputation that’s already used by an present object within the database. To repair this error, you will have to decide on a unique identify for the brand new object you are attempting to create.Ora 00955 Name Is Already Used By An Existing Object

Alternatively, you may drop the present object with the identical identify and then re-create it with the specified identify. In this information, you’ll be taught the varied causes of the error and find out how to clear up it.

Why Am I Getting This Error?

The motive you might be getting the ora 00955 identify is already used by an present object error is that you’re attempting to create a brand new object (corresponding to a desk, index, or sequence) with a reputation that’s already used by an present object within the database.

For instance, if you’re attempting to create a desk with the identify “employees” and you get this error, it signifies that there’s already a desk within the database with the identical identify. You can both select a unique identify for the brand new desk, or you may drop the prevailing “employees” desk and then re-create it with the identify you need.

– Oracle Precaution

This error is triggered by the truth that Oracle doesn’t enable two objects in the identical schema to have the identical identify. Each object within the database will need to have a singular identify, and Oracle enforces this constraint to forestall confusion and be sure that objects could be accessed and modified appropriately.1674710948 623 Ora 00955 Name Is Already Used By An Existing Object

If you attempt to create a brand new object with a reputation that’s already in use by an present object, Oracle will increase this error to stop you from overwriting the prevailing object.

You may also set off this error in Oracle Warehouse Builder should you perform an improve set up after altering the first key or distinctive index of the desk. The motive is should you generate a major key on the desk, your system creates a singular index for the first key columns and retains a connection between the first key and the distinctive index.

– Various Versions of This Error

One of the most typical variations of this error is ora-00955 identify is already used by an present object create desk. This error happens while you attempt to create a brand new desk with a reputation that’s already used by an present object within the database. Below are a few of the common variations of the error in query that you could be set off:

  • Ora-00955: identify is already used by an present object whereas creating synonym: This error can happen while you attempt to create a synonym with a reputation that’s already used by an present object within the database. A synonym is an alias for a database object, corresponding to a desk, view, or saved process.
  • Ora-00955 identify is already used by an present object materialized view: The error can come up while you attempt to create a materialized view with a reputation that’s already used by an present object within the database. A materialized view is a pre-computed view that shops the outcomes of a question in a desk, permitting customers to entry the outcomes sooner than in the event that they have been to execute the question from scratch.
  • Expdp ora-00955: identify is already used by an present object: You can set off this error while you attempt to use the expdp utility to export information from a database and the identify of the dump file you might be utilizing is already used by an present object within the database. expdp (Export Data Pump) is a utility in Oracle that permits you to export information from a database to a file or set of recordsdata.
  • Name is already used by an present object sql: This error will happen in SQL while you attempt to create a brand new object (corresponding to a desk, index, or sequence) with a reputation that’s already used by an present object within the database.
  • Ora-00955: identify is already used by an present object liquibase: You will set off this error while you attempt to use Liquibase to use a database change set that features an object with a reputation that’s already used by an present object within the database. Liquibase is a database model management software that permits you to observe, handle, and apply modifications to your database schema.
  • Ora-00955 however object doesn’t exist: The error can happen even when the item with the identical identify doesn’t really exist within the database. This can occur if there’s a drawback with the database metadata or if the item has been dropped, however the metadata has not been correctly up to date.
  • Ora 00955 identify is already used by an present object create or change: This error can happen while you attempt to use the CREATE OR REPLACE possibility to change an present object, and the identify of the item you are attempting to create is already used by an present object within the database.
READ :  ¿Qué es la fuerza molecular?

How To Solve the Error

To clear up this error, you will have to make sure that you’re utilizing distinctive names for all objects within the database. You can do that by rigorously selecting names in your objects that aren’t already in use or by utilizing a naming conference that ensures uniqueness (corresponding to including a prefix or suffix to the item identify). Here are some things you want to bear in mind when resolving this error:

  1. Identify the item that’s inflicting the battle: Use the USER_OBJECTS view or the ALL_OBJECTS view to seek out the item that has the identical identify as the item you are attempting to create.
  2. Choose a unique identify for the brand new object: If you do not want the prevailing object, you may merely select a unique identify for the brand new object. This will assist you to create a brand new object with none conflicts.
  3. Drop the prevailing object and re-create it with a unique identify: If you have to maintain the prevailing object however wish to use a unique identify for it, you may drop the prevailing object and then re-create it with the specified identify. Be certain to take any obligatory precautions earlier than dropping the item, corresponding to backing up the info or making a schema export.
  4. Alter the prevailing object to make use of a unique identify: If the prevailing object is a desk or a view, you need to use the ALTER TABLE or ALTER VIEW assertion to vary the identify of the item. This will assist you to maintain the prevailing object and use the specified identify for it.
READ :  The Only Guide You Need

Here is a fast breakdown of the varied options you may implement while you come throughout this error:

– Solution 1: Use a Unique Name

The finest approach to clear up this error is to make use of a singular identify in your desk, view, synonyms, index, sequence, or every other object. You ought to double-check to ensure you aren’t duplicating your code with out altering the identify.1674710949 622 Ora 00955 Name Is Already Used By An Existing Object

Also, maintain observe of the names you employ in your database objects to keep away from this error. While at it, ensure you don’t use a generic identify you may have already used within the database or database objects.

– Solution 2: Drop Existing Database Object

Another manner you may clear up this error is by dropping the prevailing database object with the identical identify and changing it with a brand new one. You can drop the previous object and create a brand new one. Suppose you may have a desk by the identify of staff however wish to use the identify in one other manner. You can drop that desk and create a brand new one, as proven under:

DROP TABLE staff;

CREATE TABLE new_employees (

employee_id NUMBER,

first_name VARCHAR2(30),

last_name VARCHAR2(30),

wage NUMBER

);

Note that you will want to have the suitable permissions to drop and re-create objects within the database.

READ :  How to Solve R Warning: `summarise()` has grouped output by ‘X’. You can override using the `.groups` argument

– Solution 3: Use the Create or Replace Option

In oracle, you need to use the CREATE OR REPLACE possibility to interchange any present merchandise in your database. The CREATE OR REPLACE possibility in Oracle permits you to create a brand new object (corresponding to a saved process, perform, bundle, or set off) or change an present object with a brand new definition.

The CREATE OR REPLACE possibility is beneficial in conditions the place you wish to modify the definition of an present object however don’t wish to drop and re-create the item. Using CREATE OR REPLACE permits you to make modifications to the item definition with out shedding any information or invalidating any dependencies on the item.

Here is an instance of how you need to use the CREATE OR REPLACE possibility to change a saved process:

CREATE OR REPLACE PROCEDURE update_salary (p_employee_id IN NUMBER, p_new_salary IN NUMBER)

AS

BEGIN

UPDATE staff

SET wage = p_new_salary

WHERE employee_id = p_employee_id;

END;

In this instance, the update_salary saved process is being modified to replace the wage column of the workers’ desk. If the saved process didn’t exist already, it will be created with the brand new definition. If the saved process did exist already, its definition would get replaced with the brand new one.

Note that while you use CREATE OR REPLACE, any permissions or grants that have been beforehand granted on the item shall be retained. You may even not must replace any dependencies on the item, as the item’s identify and its signature will stay the identical.

However, you have to be cautious when utilizing CREATE OR REPLACE, as it could possibly overwrite the definition of an present object with out warning. It is a good suggestion to completely take a look at any modifications you make to an object earlier than utilizing CREATE OR REPLACE to make sure that the modified object behaves as anticipated.

Conclusion

In this information, you may have realized the numerous causes and options to the ora 00955 identify is already used by an present object error. Here is a abstract of the information:

  • The error arises while you attempt to create a brand new object with a reputation that exists as an object within the database
  • It arises from the failure to maintain observe of the names you give your database and database objects
  • To clear up the error, you need to set up the item inflicting it, select a brand new identify or alter the prevailing identify

With this understanding, you may be in a greater place to clear up this error when it arises sooner or later.

Leave a Reply

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