I'm familiar with the issue behind ORA-01775: looping chain of synonyms, but is there any trick to debugging it, or do I just have to "create or replace" my way out of it?
Is there a way to query the schema or whatever to find out what the current definition of a public synonym is?
Even more awesome would be a graphical tool, but at this point, anything would be helpful.
As it turns out, the problem wasn't actually a looping chain of synonyms, but the fact that the synonym was pointing to a view that did not exist.
Oracle apparently errors out as a looping chain in this condition.
The data dictionary table DBA_SYNONYMS has information about all the synonyms in a database. So you can run the query
SELECT table_owner, table_name, db_link
FROM dba_synonyms
WHERE owner = 'PUBLIC'
AND synonym_name = <<synonym name>>
to see what the public synonym currently points at.
Today I got this error, and after debugging I figured out that the actual tables were misssing, which I was referring using synonyms. So I suggest - first check that whether the tables exists!! :-))
While Jarrod's answer is a good idea, and catches a broader range of related problems, I found this query found in Oracle forums to more directly address the (originally stated) issue:
select owner, synonym_name, connect_by_iscycle CYCLE
from dba_synonyms
where connect_by_iscycle > 0
connect by nocycle prior table_name = synonym_name
and prior table_owner = owner
union
select 'PUBLIC', synonym_name, 1
from dba_synonyms
where owner = 'PUBLIC'
and table_name = synonym_name
and (table_name, table_owner) not in (select object_name, owner from dba_objects
where object_type != 'SYNONYM')
https://community.oracle.com/message/4176300#4176300
You will not have to wade through other kinds of invalid objects. Just those that are actually in endless loops.
I had a similar problem, which turned out to be caused by missing double quotes off the table and schema name.
http://ora-01775.ora-code.com/ suggests:
ORA-01775: looping chain of synonyms
Cause: Through a series of CREATE synonym statements, a synonym was defined that referred to itself. For example, the following definitions are circular:
CREATE SYNONYM s1 for s2 CREATE SYNONYM s2 for s3 CREATE SYNONYM s3 for s1
Action: Change one synonym definition so that it applies to a base table or view and retry the operation.
For me, the table name and the synonym both existed but under different owner names. I re-created the tables under the owner name that matched the owner name in synonyms.
I used the queries posted by @Mahi_0707