I encounter the looping chain of synonyms error
Reason:-
ORA-01775: looping chain of synonyms – Basically means that you created a synonym that points to another object in a circle.
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
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
Conclusion:-
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.
