Create Synonym Failed with ORA-01471: cannot create a synonym with same name as object

advertisements

_____________________________________________________________________________________________________________________

Problem Description:

Create synonym command failed with ORA-01471: cannot create a synonym with same name as object.

Error Description:

As the error says you cannot create an object with the same name of an existing object.
In this above error you are trying to create a private synonym with the same name as the underlying object. You should use different name for the synonym to avoid this error. If really you want a synonym with the same name of the underlying object you can create a public synonym which will solve your issue.

Advantages and Features of Public Synonym

Public synonyms are accessible to all users in the database.
Don’t create a public synonym with the same name as the exiting schema. In case if you do the same, all the PL/SQL units that use that name will be invalidated.
If you create a public synonym and it subsequently has dependent tables or dependent valid user-defined object types, then you cannot create another database object of the same name as the synonym in the same schema as the dependent objects.

See example

SQL> create synonym employee for employee;

create synonym employee for employee

ERROR at line 1:

ORA-01471: cannot create a synonym with same name as object

SQL> create public synonym employee for employee;

Synonym created.

SQL> select object_name, object_type, owner from all_objects where object_name ='EMPLOYEE'

SQL> /

OBJECT_NAME OBJECT_TYPE OWNER

------------------------------ ------------------- ------------------

EMPLOYEE SYNONYM PUBLIC