How to debug ORA-01775: looping chain of synonyms?

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

I had a similar problem, which turned out to be caused by missing double quotes off the table and schema name.

Answer 5

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!! :-))