While Accessing Public Synonym Getting Error ORA-1775: looping chain of synonyms


APPLIES TO:
Oracle Database – Enterprise Edition – Version 11.2.0.3 and later


SYMPTOMS:
User created a public synonym with the same name as the table name, while accessing the synonym getting the following error.

ORA-01775: looping chain of synonyms


CAUSE:
The syntax used for public synonym creation was wrong. The schema name is not specified while creating the public synonym.

create public synonym <synonym_name> for <tablename>; 

SQL> conn TESTER/TESTER
Connected.
SQL> create table test_synonym ( n number);
Table created.
SQL>
SQL> insert into test_synonym values (100);
1 row created.
SQL> commit;
Commit complete.
SQL> conn / as sysdba
Connected.
SQL> show user
USER is “SYS”
SQL> create public synonym test_synonym for test_synonym;     >>>>>>>>>>>>> Didn’t specify the schema in which the object resides
Synonym created.
SQL> select count (*) from test_synonym;      
select count (*) from test_synonym
*
ERROR at line 1:
ORA-01775: looping chain of synonyms
SOLUTION:
Create the synonym using the correct syntax.
SQL> conn / as sysdba
Connected.
SQL> show user
USER is “SYS”

SQL>
SQL> drop public synonym test_synonym;

Synonym dropped.

SQL> create public synonym test_synonym for TESTER.test_synonym;

Synonym created.

SQL> select count (*) from test_synonym;

COUNT(*)
———-
1

SQL>

  • December 24, 2018 | 30 views
  • Comments