ORA-01775 looping chain of synonyms

I got ” ORA-01775 looping chain of synonyms ”  error in Oracle database.

ORA-01775 looping chain of synonyms

Details of error are as follows.

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.

Getting error 'ORA-01775: looping chain of synonyms' on select or describe of a table.
Verified that only one synonym and object exists but getting error as follows:

SQL> select count(*) from <table_name>;
select count(*) from <table_name>
*
ERROR at line 1:
ORA-01775: looping chain of synonyms

Error while writing to audit trail

This ORA-02002 errors are related with 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

Change one synonym definition so that it applies to a base table or view and retry the operation.

Based on the stack trace; found similar Bug 9648686 – ORA-01775 FOR A TABLE WITH FUNCTION BASED INDEX, AFTER THE FUNCTION WAS DROPPED

There is a function and a PUBLIC synonym for the function with the same name.
The function is used in a function-based index and everything is OK if the function exists.

If we drop the function, but the associated public synonym still exists, every attempt to reference the function results in ORA-1775 Looping chain of synonyms; which seems like correct behavior according to Note 392705.1.

The problem in this case is that we are not even able to perform a simple select from a base table, even without any filter, which seems like incorrect behavior. If we drop the PUBLIC synonym for the non-existing function, we can again select the table, although there is of course no function used by the existing function-based index.

In this case the function does not exist, however, there is a public synonym pointing to that function.

To solve this error, Drop the public synonym for the function.

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )