Looping chain of synonyms - ORA-01775

Looping chain of synonyms - ORA-01775

Have you seen this?
                  ORA-01775: looping chain of synonyms
                  01775. 00000 -  "looping chain of synonyms"
                  *Cause:    
                  *Action:
                   Error at Line: 85 Column: 14

When do you see this error message? In this article, we will duplicate the issue and then find out the cause. We have a customer table with 9 records. We will create a private synonym to employee table for application to use.

-- check record count
SELECT COUNT(*) FROM CUSTOMER;
COUNT(*)
--------
       9

--- create  private synonym
CREATE SYNONYM MY_TEAM FOR SCOTT.CUSTOMER;
synonym MY_TEAM created.

--verify synonym
select count(*) from MY_TEAM;
COUNT(*)
--------
       9

--checking synonym detail
select * from dba_synonyms where synonym_name = 'MY_TEAM';
                                                                                                                               
This above query shows what object the synonym points to. In our case, synonym my_team points to customer table of scott schema. A request came in to drop customer table from scott schema, Business no longer needed data from scott.customer table. Dropping will save disk space and makes the database clutter free.

What happens when the table is dropped and the application is hitting the synonym. Let’s see how Oracle react to that.

drop table scott.customer;
table dropped.

Now, lets see how your synonym behaves it?
select * from my_team;

ORA-01775: looping chain of synonyms
01775. 00000 -  "looping chain of synonyms"
*Cause:    
*Action:
Error at Line: 85 Column: 14

We have replicated the issue successfully. You now KNOW the fix to a looping chain of synonym problem, don’t you?
Best Practices: Always remember, if you are asked to drop an object, you should check the dependencies for that object. The dependent objects need to be dropped/fixed as well or you will make dependent objects invalid. Checking dependencies and taking appropriate action will help you prevent from seeing weird Oracle error messages. Troubleshooting weird oracle message later will be stressful and time consuming. Act right the first time database geek!!
Quiz:
How do you check object dependencies?

Script to all all the orphan synonyms or synonyms that returns looping chain of synonym.

SELECT s.* 
FROM   dba_synonyms s 
       left join dba_objects o 
              ON o.owner = s.table_owner 
                 AND o.object_name = s.table_name 
WHERE  o.object_name IS NULL;

As a DBA, you are required to proactively monitor the synonym and alert the ones that are pointed to dropped objects. The below script will list all the synonyms where the tables are dropped. You are required to test this script before implementing on production.