ORA-01775: looping chain of synonyms

By Franck Pachot

.
This error message is misleading. You may encounter it when you expect ORA-00942: table or view does not exist. Let’s explain

I’m connected as SCOTT and create a PUBLIC SYNONYM for an object that do not exists:

SQL> create public synonym MONEY for NOTHING;
Synonym created.

No error message.
Only when I read it I have an error message telling me that there are no table or view behind it:

SQL> select * from NOTHING;
select * from NOTHING
              *
ERROR at line 1:
ORA-00942: table or view does not exist

Let’s do the same but call it BONUS instead of MONEY:

SQL> create public synonym BONUS for NOTHING;
Synonym created.
 
SQL> select * from BONUS;
no rows selected

No error here. Why? because I’ve a table that is called BONUS. So the name is resolved with the table and the synonym is not even tried.

I’ll now drop that synonym and create it for the table BONUS. Same name for the public synonym and for the table.

SQL> drop public synonym BONUS;
Synonym dropped.
 
SQL> create public synonym BONUS for BONUS;
Synonym created.

As user SCOTT, when I query BONUS the name is resolved as the table:

SQL> show user
USER is "SCOTT"
SQL> select * from BONUS;
no rows selected

As another user, when I query BONUS the name is resolved as the synonym, which finally reads SCOTT.BONUS:

SQL> show user
USER is "SCOTT"
SQL> select * from BONUS;
no rows selected

In 12c it is easy to see the final query:

SQL> variable c clob
SQL> exec dbms_utility.expand_sql_text('select * from BONUS',:c);
PL/SQL procedure successfully completed.
 
SQL> print c
 
C
----------------------------------------------------------------------------------------------------------
SELECT "A1"."ENAME" "ENAME","A1"."JOB" "JOB","A1"."SAL" "SAL","A1"."COMM" "COMM" FROM "SCOTT"."BONUS" "A1"

But now, what happens when we drop the table?

SQL> drop table SCOTT.BONUS;
Table dropped.

Do you expect a ORA-00942: table or view does not exist?

SQL> select * from BONUS;
select * from BONUS
              *
ERROR at line 1:
ORA-01775: looping chain of synonyms

Here is the ‘looping chain of synonyms’. I ask for BONUS. The name resolution first check for an object in my schema, but there are none:

SQL> select object_type from user_objects where object_name='BONUS';
no rows selected

Then it looks for public synonym and there is one:

SQL> select object_type from all_objects where owner='PUBLIC' and object_name='BONUS';
 
OBJECT_TYPE
-----------------------
SYNONYM

So we check what it is a synonym for:

SQL> select table_owner,table_name from all_synonyms where owner='PUBLIC' and synonym_name='BONUS';
 
TABLE_OWNER  TABLE_NAME
------------ ------------
SCOTT        BONUS

And there it is interesting. Besides the column names that includes ‘TABLE’ a synonym can reference any object. So it’s not just replacing the synonym with ‘SCOTT.BONUS’ which would raise an ORA-00942. It is doing name resolution of BONUS in the context of the user SCOTT. Something similar to:

SQL> alter session set current_schema=SCOTT;
Session altered.
SQL> select * from BONUS;

And then, what do you expect from that? There is no table named BONUS but there is a public synonym… and you’re back to the begining:

select * from BONUS
              *
ERROR at line 1:
ORA-01775: looping chain of synonyms

Most of the time, you don’t have synonyms that reference other synonyms, so you don’t really have a ‘chain’ of synonyms. Except when there is only synonym in the namespace and it’s a self-reference loop. So if you see ORA-01775, check if the referenced object is not missing.