ORA-01775
- synonym_name: The synonym that is translating to itself.
Cause
Translation of a synonym resolved to the same synonym. This is caused by a chain of object definitions including multiple private or public synonyms.
Action
Review the object definitions to determine which synonym resolves back to the original and change one or more of the definitions to break the cycle.
Additional Information
This is an example of a chain of object definitions including multiple synonyms that resolves to itself:
SQL> CREATE SYNONYM s1 for s2;
Synonym created.
SQL> CREATE SYNONYM s2 for s3;
Synonym created.
SQL> CREATE SYNONYM s3 for s1;
Synonym created.
SQL> SELECT * FROM s1;
SELECT * FROM s1
*
ERROR at line 1:
ORA-01775: looping chain of synonyms
Here, synonym s1 points to synonym s2, which points to
synonym s3, which in turn points back to synonym s1.
If you query s1, your statement will never arrive at an
actual table or view but will end up in an infinite loop.
Another example that involves an unanticipated public synonym resolution:
SQL> CREATE TABLE BOOKS (IS INT);
Table created.
SQL> CREATE SYNONYM B FOR BOOKS;
Synonym created.
SQL> CREATE PUBLIC SYNONYM BOOKS FOR B;
Synonym created.
SQL> DROP TABLE BOOKS;
Table dropped.
SQL> SELECT * FROM B:
SELECT * FROM B
*
ERROR at line 1:
ORA-01775: looping chain of synonyms
In this scenario, the user has a valid synonym translation from
synonym B to the table BOOKS. However, when the table BOOKS
is dropped, the synonym translation for synonym B now resolves to
the public synonym BOOKS which, in turn, resolves to the (private)
synonym B creating an infinite loop. During synonym translations,
the database first looks at private objects in the current schema,
such as tables, views, and synonyms, followed by public synonyms.
In this example, once the table BOOKS is dropped, the synonym
translation finds the public synonym BOOKS which points back to
a synonym B creating the infinite loop.