When upgrading spotlight user getting an error ORA-01775 looping chain of synonyms ORA-06512: (4335230)

ERROR at line 1:
ORA-01775: looping chain of synonyms
ORA-06512: at line 187

Looping synonym

Assuming that your users name is Spoltight_user run the following two queries to see who is the user and what sysonyms needs to be dropped.

SELECT owner, object_name, object_type
FROM dba_objects
WHERE object_name = Spotlight_user;

SELECT s.owner,
s.synonym_name,
s.table_owner,
s.table_name,
o.object_type
FROM dba_synonyms s, dba_objects o
WHERE s.synonym_name = Spoltight_user
AND o.owner = s.table_owner
AND o.object_name = s.table_name;


Drop the Spoltight_user user and drop all synonyms that remained that referenced to the Spotlight_user schema

To drop the synonym by running the following command as SYS:

drop public synonym Spoltight_user;