Contents
How do you fix looping chains of synonyms in Oracle?
ORA-01775: looping chain of synonyms – Basically means that you created a synonym that points to another object in a circle. In order to fix the above problem you need to have one of the synonyms in the chain point to an object like below. SQL> drop synonym s3; Synonym dropped.
How do you drop a synonym in Oracle?
The syntax to drop a synonym in Oracle is: DROP [PUBLIC] SYNONYM [schema .] synonym_name [force];
How do you drop a public synonym?
To drop a PUBLIC synonym, you must have the DROP PUBLIC SYNONYM system privilege. You must specify PUBLIC to drop a public synonym. You cannot specify schema if you have specified PUBLIC . Specify the schema containing the synonym.
How do I create a synonym in Oracle?
Introduction to Oracle CREATE SYNONYM statement
- First, specify the name of the synonym and its schema.
- Second, specify the object for which you want to create the synonym after the FOR keyword.
- Third, use the OR REPLACE option if you want to re-create the synonym if it already exists.
How do I create a user synonym in Oracle?
What is Dba_synonyms?
DBA_SYNONYMS describes all synonyms in the database. Its columns are the same as those in ALL_SYNONYMS .
How to fix ora-01775 looping chain of synonyms?
Action: Change one synonym definition so that it applies to a base table or view and retry the operation. It should also be noted that a user on Oracle DBA Forums mentioned that ORA-01775 may be thrown because of a synonym that was defined and referred to itself:
How to do the looping chain of synonyms in SQL?
SQL> select * from BONUS; select * from BONUS * ERROR at line 1: ORA-01775: looping chain of synonyms Here is the ‘looping chain of synonyms’. I ask for BONUS. The name resolution first check for an object in my schema, but there are none: Then it looks for public synonym and there is one: So we check what it is a synonym for:
How to debug Oracle looping chain of synonyms?
Oracle apparently errors out as a looping chain in this condition. If you are using TOAD, go to View>Toad Options>Oracle>General and remove TOAD_PLAN_TABLE from EXPLAIN PLAN section and put PLAN_TABLE The data dictionary table DBA_SYNONYMS has information about all the synonyms in a database.
Why was ora-01775 thrown in Oracle DBA?
It should also be noted that a user on Oracle DBA Forums mentioned that ORA-01775 may be thrown because of a synonym that was defined and referred to itself: Change one synonym definition so that it applies to a base table or view and retry the operation.