How to debug ORA-01775 looping chain of synonyms

Question

I m familiar with the issue behind ORA-01775  looping chain of synonyms  but is there any trick to debugging it  or do I just have to  create or replace  my way out of it     Is there a way to query the schema or whatever to find out what the current definition of a public synonym is     Even more awesome would be a graphical tool  but at this point  anything would be helpful

User · Accepted Answer

As it turns out  the problem wasn t actually a looping chain of synonyms  but the fact that the synonym was pointing to a view that did not exist   Oracle apparently errors out as a looping chain in this condition

User · Answer

I m using the following sql to find entries in all synonyms where there is no corresponding object for the object name  in user objects     select       from all synonyms     where table owner    SCOTT        and synonym name not like            and table name not in          select object name from user objects          where object type in               TABLE    VIEW    PACKAGE    SEQUENCE               PROCEDURE    FUNCTION    TYPE

User · Answer

The data dictionary table DBA SYNONYMS has information about all the synonyms in a database   So you can run the query  SELECT table owner  table name  db link   FROM dba synonyms   WHERE owner           PUBLIC     AND synonym name    lt  lt synonym name gt  gt    to see what the public synonym currently points at

User · Answer

We encountered this error today   This is how we debugged and fixed it    Package went to invalid state due to this error ORA-01775  With the error line number   We went thru the package body code and found the code which was trying to insert data into a table  We ran below queries to check if the above table and synonym exists   SELECT   FROM DBA TABLES WHERE TABLE NAME     amp TABLE NAME    -- No rows returned  SELECT   FROM DBA SYNONYMS WHERE SYNONYM NAME     amp SYNONYM NAME   -- 1 row returned  With this we concluded that the table needs to be re- created  As the synonym was pointing to a table that did not exist  DBA team re-created the table and this fixed the issue

User · Answer

We encountered this error today   This is how we debugged and fixed it    Package went to invalid state due to this error ORA-01775  With the error line number   We went thru the package body code and found the code which was trying to insert data into a table  We ran below queries to check if the above table and synonym exists   SELECT   FROM DBA TABLES WHERE TABLE NAME     amp TABLE NAME    -- No rows returned  SELECT   FROM DBA SYNONYMS WHERE SYNONYM NAME     amp SYNONYM NAME   -- 1 row returned  With this we concluded that the table needs to be re- created  As the synonym was pointing to a table that did not exist  DBA team re-created the table and this fixed the issue

User · Answer

The less intuitive solution to this error code seems to be problems with the objects that the synonym is pointing to   Here is my SQL for finding synonyms that point to erroneous objects   SELECT S OWNER as SYN OWNER  S SYNONYM NAME as SYN NAME      S TABLE OWNER as OBJ OWNER  S TABLE NAME as OBJ NAME      CASE WHEN O OWNER is null THEN  MISSING  ELSE O STATUS END as OBJ STATUS FROM DBA SYNONYMS S     LEFT JOIN DBA OBJECTS O ON S TABLE OWNER   O OWNER AND S TABLE NAME   O OBJECT NAME WHERE O OWNER is null     OR O STATUS     VALID

User · Answer

Try this select to find the problematic synonyms  it lists all synonyms that are pointing to an object that does not exist  tables views sequences packages  procedures  functions   SELECT   FROM dba synonyms WHERE table owner    USER      AND           NOT EXISTS               SELECT               FROM dba tables             WHERE dba synonyms table name   dba tables TABLE NAME                       AND NOT EXISTS               SELECT               FROM dba views             WHERE dba synonyms table name   dba views VIEW NAME                       AND NOT EXISTS               SELECT               FROM dba sequences             WHERE dba synonyms table name   dba sequences sequence NAME                       AND NOT EXISTS               SELECT               FROM dba dependencies             WHERE type IN                        PACKAGE                        PROCEDURE                        FUNCTION                                        AND dba synonyms table name   dba dependencies NAME

User · Answer

The data dictionary table DBA SYNONYMS has information about all the synonyms in a database   So you can run the query  SELECT table owner  table name  db link   FROM dba synonyms   WHERE owner           PUBLIC     AND synonym name    lt  lt synonym name gt  gt    to see what the public synonym currently points at

User · Answer

I had a similar problem  which turned out to be caused by missing double quotes off the table and schema name

User · Answer

I had a function defined in the wrong schema and without a public synonym  I e  my proc was in schema  Dogs  and the function was in schema  Cats   The function didn t have a public synonym on it to allow Dogs to access the cats  function

User · Answer

I had a function defined in the wrong schema and without a public synonym  I e  my proc was in schema  Dogs  and the function was in schema  Cats   The function didn t have a public synonym on it to allow Dogs to access the cats  function

User · Answer

ORA-01775  looping chain of synonyms I faced the above error while I was trying to compile a Package which was using an object for which synonym was created however underlying object was not available

User · Answer

http   ora-01775 ora-code com  suggests   ORA-01775   looping chain of synonyms Cause     Through a series of CREATE synonym statements  a synonym was defined that referred to itself  For example  the following definitions are circular  CREATE SYNONYM s1 for s2 CREATE SYNONYM s2 for s3 CREATE SYNONYM s3 for s1 Action    Change one synonym definition so that it applies to a base table or view and retry the operation

User · Answer

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

User · Answer

http   ora-01775 ora-code com  suggests   ORA-01775   looping chain of synonyms Cause     Through a series of CREATE synonym statements  a synonym was defined that referred to itself  For example  the following definitions are circular  CREATE SYNONYM s1 for s2 CREATE SYNONYM s2 for s3 CREATE SYNONYM s3 for s1 Action    Change one synonym definition so that it applies to a base table or view and retry the operation

User · Answer

We had the same ORA-01775 error but in our case  the schema user was missing some  grant select  on a couple of the public synonyms

User · Answer

The data dictionary table DBA SYNONYMS has information about all the synonyms in a database   So you can run the query  SELECT table owner  table name  db link   FROM dba synonyms   WHERE owner           PUBLIC     AND synonym name    lt  lt synonym name gt  gt    to see what the public synonym currently points at

User · Answer

For me  the table name and the synonym both existed but under different owner names  I re-created the tables under the owner name that matched the owner name in synonyms    I used the queries posted by  Mahi 0707

User · Answer

The less intuitive solution to this error code seems to be problems with the objects that the synonym is pointing to   Here is my SQL for finding synonyms that point to erroneous objects   SELECT S OWNER as SYN OWNER  S SYNONYM NAME as SYN NAME      S TABLE OWNER as OBJ OWNER  S TABLE NAME as OBJ NAME      CASE WHEN O OWNER is null THEN  MISSING  ELSE O STATUS END as OBJ STATUS FROM DBA SYNONYMS S     LEFT JOIN DBA OBJECTS O ON S TABLE OWNER   O OWNER AND S TABLE NAME   O OBJECT NAME WHERE O OWNER is null     OR O STATUS     VALID

User · Answer

While Jarrod s answer is a good idea  and catches a broader range of related problems  I found this query found in Oracle forums to more directly address the  originally stated  issue   select owner  synonym name  connect by iscycle CYCLE from dba synonyms where connect by iscycle  gt  0 connect by nocycle prior table name   synonym name and prior table owner   owner union select  PUBLIC   synonym name  1 from dba synonyms where owner    PUBLIC  and table name   synonym name and  table name  table owner  not in  select object name  owner from dba objects where object type     SYNONYM     https   community oracle com message 4176300 4176300  You will not have to wade through other kinds of invalid objects  Just those that are actually in endless loops

User · Answer

We had the same ORA-01775 error but in our case  the schema user was missing some  grant select  on a couple of the public synonyms

User · Answer

ORA-01775  looping chain of synonyms I faced the above error while I was trying to compile a Package which was using an object for which synonym was created however underlying object was not available

User · Answer

A developer accidentally wrote code that generated and ran the following SQL statement CREATE OR REPLACE PUBLIC SYNONYM  DUAL  FOR  DUAL   which caused select   from dba synonyms where table name    DUAL    to return PUBLIC DUAL    SOME USER   DUAL rather than PUBLIC   DUAL    SYS DUAL   We were able to fix it  thanks to How to recreate public synonym  quot DUAL quot    by running  ALTER SYSTEM SET   SYSTEM TRIG ENABLED  FALSE SCOPE MEMORY  CREATE OR REPLACE PUBLIC SYNONYM DUAL FOR SYS DUAL  ALTER SYSTEM SET   SYSTEM TRIG ENABLED  true SCOPE MEMORY

User · Answer

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

User · Answer

Today I got this error  and after debugging I figured out that the actual tables were misssing  which I was referring using synonyms  So I suggest - first check that whether the tables exists    -

User · Answer

Step 1  See what Objects exist with the name   select   from all objects where object name   upper   amp object name      It could be that a Synonym exists but no Table     Step 2  If that s not the problem  investigate the Synonym   select   from all synonyms where synonym name   upper   amp synonym name      It could be that an underlying Table or View to that Synonym is missing

User · Answer

While Jarrod s answer is a good idea  and catches a broader range of related problems  I found this query found in Oracle forums to more directly address the  originally stated  issue   select owner  synonym name  connect by iscycle CYCLE from dba synonyms where connect by iscycle  gt  0 connect by nocycle prior table name   synonym name and prior table owner   owner union select  PUBLIC   synonym name  1 from dba synonyms where owner    PUBLIC  and table name   synonym name and  table name  table owner  not in  select object name  owner from dba objects where object type     SYNONYM     https   community oracle com message 4176300 4176300  You will not have to wade through other kinds of invalid objects  Just those that are actually in endless loops

User · Answer

If you are compiling a PROCEDURE  possibly this is referring to a table or view that does not exist as it is created in the same PROCEDURE  In this case the solution is to make the query declared as String eg v query     insert into table select   from table2 and then execute immediate on v query   This is because the compiler does not yet recognize the object and therefore does not find the reference  Greetings

User · Answer

If you are compiling a PROCEDURE  possibly this is referring to a table or view that does not exist as it is created in the same PROCEDURE  In this case the solution is to make the query declared as String eg v query     insert into table select   from table2 and then execute immediate on v query   This is because the compiler does not yet recognize the object and therefore does not find the reference  Greetings

User · Answer

Today I got this error  and after debugging I figured out that the actual tables were misssing  which I was referring using synonyms  So I suggest - first check that whether the tables exists    -

User · Answer

Try this select to find the problematic synonyms  it lists all synonyms that are pointing to an object that does not exist  tables views sequences packages  procedures  functions   SELECT   FROM dba synonyms WHERE table owner    USER      AND           NOT EXISTS               SELECT               FROM dba tables             WHERE dba synonyms table name   dba tables TABLE NAME                       AND NOT EXISTS               SELECT               FROM dba views             WHERE dba synonyms table name   dba views VIEW NAME                       AND NOT EXISTS               SELECT               FROM dba sequences             WHERE dba synonyms table name   dba sequences sequence NAME                       AND NOT EXISTS               SELECT               FROM dba dependencies             WHERE type IN                        PACKAGE                        PROCEDURE                        FUNCTION                                        AND dba synonyms table name   dba dependencies NAME

User · Answer

I had a similar problem  which turned out to be caused by missing double quotes off the table and schema name

User · Answer

http   ora-01775 ora-code com  suggests   ORA-01775   looping chain of synonyms Cause     Through a series of CREATE synonym statements  a synonym was defined that referred to itself  For example  the following definitions are circular  CREATE SYNONYM s1 for s2 CREATE SYNONYM s2 for s3 CREATE SYNONYM s3 for s1 Action    Change one synonym definition so that it applies to a base table or view and retry the operation

User · Answer

I m using the following sql to find entries in all synonyms where there is no corresponding object for the object name  in user objects     select       from all synonyms     where table owner    SCOTT        and synonym name not like            and table name not in          select object name from user objects          where object type in               TABLE    VIEW    PACKAGE    SEQUENCE               PROCEDURE    FUNCTION    TYPE

User · Answer

http   ora-01775 ora-code com  suggests   ORA-01775   looping chain of synonyms Cause     Through a series of CREATE synonym statements  a synonym was defined that referred to itself  For example  the following definitions are circular  CREATE SYNONYM s1 for s2 CREATE SYNONYM s2 for s3 CREATE SYNONYM s3 for s1 Action    Change one synonym definition so that it applies to a base table or view and retry the operation

User · Answer

A developer accidentally wrote code that generated and ran the following SQL statement CREATE OR REPLACE PUBLIC SYNONYM  DUAL  FOR  DUAL   which caused select   from dba synonyms where table name    DUAL    to return PUBLIC DUAL    SOME USER   DUAL rather than PUBLIC   DUAL    SYS DUAL   We were able to fix it  thanks to How to recreate public synonym  quot DUAL quot    by running  ALTER SYSTEM SET   SYSTEM TRIG ENABLED  FALSE SCOPE MEMORY  CREATE OR REPLACE PUBLIC SYNONYM DUAL FOR SYS DUAL  ALTER SYSTEM SET   SYSTEM TRIG ENABLED  true SCOPE MEMORY

User · Answer

For me  the table name and the synonym both existed but under different owner names  I re-created the tables under the owner name that matched the owner name in synonyms    I used the queries posted by  Mahi 0707

User · Answer

The data dictionary table DBA SYNONYMS has information about all the synonyms in a database   So you can run the query  SELECT table owner  table name  db link   FROM dba synonyms   WHERE owner           PUBLIC     AND synonym name    lt  lt synonym name gt  gt    to see what the public synonym currently points at

User · Answer

Step 1  See what Objects exist with the name   select   from all objects where object name   upper   amp object name      It could be that a Synonym exists but no Table     Step 2  If that s not the problem  investigate the Synonym   select   from all synonyms where synonym name   upper   amp synonym name      It could be that an underlying Table or View to that Synonym is missing

[sql] How to debug ORA-01775: looping chain of synonyms?

Examples related to sql

Examples related to oracle

Examples related to synonym