Home > Database, Oracle > Oracle: ORA-01775: looping chain of synonyms when querying a public synonym

Oracle: ORA-01775: looping chain of synonyms when querying a public synonym

Problem description:
You are trying to execute a query which raises the error ORA-01775:

SQL> SELECT * FROM mytestname;
SELECT * FROM mytestname
              *
ERROR at line 1:
ORA-01775: looping chain OF synonyms
 
 
SQL>

 
Cause:
If a synonym is unable to translate to the described object, because it is missing, Oracle normally would raise an “ORA-00980: synonym translation is no longer valid” error.

Example:

SQL> CREATE public synonym pubsynonym10 FOR system.asdfasdasdf;
 
Synonym created.
 
SQL> SELECT * FROM pubsynonym10;
SELECT * FROM pubsynonym10
              *
ERROR at line 1:
ORA-00980: synonym translation IS no longer valid
 
 
SQL>

 
But with 10.1 onwards, Oracle raises an “ORA-01775: looping chain of synonyms” if the accessed public synonym and the missing base table share the same name.

Example:

SQL> SHOW USER
USER IS "SYS"
SQL> 
SQL> CREATE public synonym mytestname FOR system.mytestname;
 
Synonym created.
 
SQL> 
SQL> SELECT * FROM mytestname;
SELECT * FROM mytestname
              *
ERROR at line 1:
ORA-01775: looping chain OF synonyms
 
 
SQL>

 
Problem resolution:

Check the existance of the base object referenced and fix the no longer valid translation.

Categories: Database, Oracle Tags:
  1. No comments yet.
  1. No trackbacks yet.

Connect with Facebook