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.t
AND o.object_
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.
Interested in working with me? I can be reached at pbaniya04[at]gmail.com for any questions, consulting opportunities or you may drop a line to say HELLO. Thank your again for visiting my blog and looking forward to serving you more.
Have a Database-ious Day!
Have a Database-ious Day!
This comment has been removed by a blog administrator.
ReplyDeletenice blog
ReplyDeleteThis comment has been removed by a blog administrator.
ReplyDeletenice
ReplyDeleteExcellent blog! I found it while surfing around on Google. Content of this page is unique as well as well researched. Appreciate it. supply chain solutions
ReplyDeleteThe declaration is split into parts. The supply shows in which the cash for the enterprise got here from, both because of a lower in belongings or an growth in liabilities.US Business Funding
ReplyDeleteMmm.. estimable to be here in your report or notify, whatever, I repute I should moreover process strong for my have website want I play some salubrious further updated busy in your location. best hospital for lasik eye surgery in delhi
ReplyDelete