ORA-01775 Looping chain of Synonyms

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%';
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s