ORA-1775 “looping chain of synonyms” can be found online quite easily. But every time they talk about a real loop of synonyms (like syn1 -> syn2 -> syn3 -> syn1) and sometime about missing objects. The solution is always “find the missing object or drop the problematic synonym.
In my case, the problematic code was a few thousand line package that got this error when I compiled its body. Looking for the missing object or problematic synonym is not really possible here, so I had to think what to do.
First, I checked if I have real “looping” synonyms. This means that synonyms are referencing other synonyms, so here is the query I used:
select * from all_synonyms
where (table_owner,table_name) in
(select owner,synonym_name from all_synonyms);
This returned no rows, so this is not the issue.
As I said, another problem can be a non existent object which is referenced by a synonym (check this blog post by Franck Pachot). The way to do that is to use ALL_DEPENDENCIES view as it contains all relations between objects in the database. So I wrote this query that selects all the dependent synonyms and generates “describe” commands that I can execute later:
select distinct 'desc ' || referenced_name
from all_dependencies where owner=user and name='BAD_PACKAGE'
and referenced_type='SYNONYM';
Then I executed the “desc” commands and for one object I got:
SQL> desc some_synonym
SP2-0749: Cannot resolve circular path of synonym "some_synonym"
I checked and there was a public synonym called “SOME_SYNONYM” but the package it was referencing did not exist. Fixing this will solve the ORA-1775 problem.