Synonyms (Part 2)

A colleague had an ORA-01775 error so I ran some tests on Oracle 11.2 to see how this might happen. First I created a table:

SQL> create table andrew (col1 number)
  2  /

Table created.

SQL>

Then I created a public synonym for that table:

SQL> create public synonym andrew for andrew
  2  /

Synonym created.

SQL>

Next I dropped the table:

SQL> drop table andrew
  2  /

Table dropped.

SQL>

Then when I tried to use the public synonym, Oracle returned an ORA-01775:

SQL> select count(*) from andrew
  2  /
select count(*) from andrew
                     *
ERROR at line 1:
ORA-01775: looping chain of synonyms

SQL>

But when I described it, I got a different error:

SQL> desc andrew
SP2-0749: Cannot resolve circular path of synonym "andrew"
SQL>

You get a similar problem if you create a public synonym for a non-existent table:

SQL> create public synonym joe for joe
  2  /

Synonym created.

SQL> desc joe
SP2-0749: Cannot resolve circular path of synonym "joe"
SQL>

You cannot do this with a private synonym as Oracle does not allow a private synonym to have the same name as the object to which it refers:

SQL> create table desmond (col1 number)

  2  /

Table created.

SQL> create synonym desmond for desmond

  2  /

create synonym desmond for desmond

*

ERROR at line 1:

ORA-01471: cannot create a synonym with same name as

object

SQL>

But, if you want to produce an ORA-01775 with private synonyms, you can do it like this:

SQL> create synonym andrew1 for andrew2
  2  /

Synonym created.

SQL> create synonym andrew2 for andrew1
  2  /

Synonym created.

SQL> select count(*) from andrew1
  2  /
select count(*) from andrew1
                     *
ERROR at line 1:
ORA-01775: looping chain of synonyms

SQL> desc andrew1
SP2-0749: Cannot resolve circular path of synonym "andrew1"
SQL>