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

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
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google