ORA-01775 looping chain of synonyms is caused through a series of CREATE synonym statements wherein a synonym was defined that referred to itself.
For example, the following definitions are circular:
CREATE SYNONYM synonym_name1 for synonym_name2
CREATE SYNONYM synonym_name2 for synonym_name3
CREATE SYNONYM synonym_name3 for synonym_name1
To resolve the issue, we need to change one synonym definition so that it applies to a base table or view and retry the operation.
Given below SQL scripts could be used to lookup the synonyms:-
SELECT TABLE_OWNER, TABLE_NAME, DB_LINK FROM DBA_SYNONYMS WHERE OWNER = 'PUBLIC' AND SYNONYM_NAME LIKE '%DATA_REQUEST%'; SELECT * FROM ALL_SYNONYMS WHERE SYNONYM_NAME LIKE '%DATA_REQUEST%'; SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME LIKE '%DATA_REQUEST%';