Oracle Looping Chain of Synonyms. ORA-01775

So I encounter the looping chain of synonyms error every once in a while and it seems pretty simple to understand, though I thought I would take a minute to demonstrate how it works.

ORA-01775: looping chain of synonyms – Basically means that you created a synonym that points to another object in a circle.

Here is an example that would cause the error:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
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
SQL> select * from s2;
select * from s2
*
ERROR at line 1:
ORA-01775: looping chain of synonyms
SQL> select * from s3;
select * from s3
*
ERROR at line 1:
ORA-01775: looping chain of synonyms

In order to fix the above problem you need to have one of the synonyms in the chain point to an object like below.

SQL> drop synonym s3;

Synonym dropped.

SQL> create table table1(col1 char(1));

Table created.

SQL> create synonym s3 for table1;

Synonym created.

SQL> select * from s1;

no rows selected

SQL> select * from s2;

no rows selected

SQL> select * from s3;

no rows selected

As you can see the looping chain is now broken and all synonyms created will point to the object created and then pointed to by s3.

If the object was dropped or didn’t exist you would get a different error all together like so:

SQL> drop table table1;

Table dropped.

SQL> select * from s3;
select * from s3
*
ERROR at line 1:
ORA-00980: synonym translation is no longer valid
SQL> select * from s2;
select * from s2
*
ERROR at line 1:
ORA-00980: synonym translation is no longer valid
SQL> select * from s1;
select * from s1
*
ERROR at line 1:
ORA-00980: synonym translation is no longer valid

Obviously if the object is recreated it will fix the error reported above.

SQL> create table table1(col1 char(1));

Table created.

SQL> select * from s3;

no rows selected

SQL> select * from s1;

no rows selected

SQL> select * from s2;

no rows selected

With that said, I hope that this helps someone wrap their head around how this error presents itself. If you need more help get in touch with one of our experts DBA’s today!