ORA-01775 – Looping Chain of Synonyms and Epic Football Punditry

In 1990, Liverpool became English League Champions for the 10th time in 15 seasons.
Despite this impressive track record, my Dad came over all Yoda-esque and confidently predicted that they would not win The Title again in his lifetime.
Since then, Liverpool have won everything else, including the Champions League – OK Dad , the European Cup – but the prediction has held.
In fact, it’s gone on so long that it probably qualifies as a prophecy by now.
Before the start of each season, I can assess Liverpool’s prospects, by simply enquiring after his health.
“Musn’t grumble, ‘cos if you do no-one bloody listens !” can be taken as a synonym for “I’ll be around for a while yet, so don’t waste your money on backing Liverpool to win it this season”.
Which brings us to the subject of this post – namely the apparently random nature of the ORA-01775 error, where synonyms are concerned…

To demonstrate what’s going on and (hopefully) why, I’ve created a table in the MIKE schema and granted SELECT on it to HR :

create table predictions (
    predictor varchar2(128), 
    prediction varchar2(4000))
/

insert into predictions values('MY DAD', 'Liverpool will not win the League again in my lifetime')
/

grant select on predictions to hr
/

As a result, I can query the table when connected as HR :

I can also create a local synonym for the table so I don’t have to remember which schema it’s in …

create or replace synonym predictions for mike.predictions;

Now, if we were to drop the table (back in the MIKE schema, remember) …

drop table predictions;

… and then attempt to reference it directly, the result is entirely, well, predictable…

Meanwhile, the synonym is unaffected by the removal of it’s underlying table …

…which means we get a different error when we use it to try to access the dropped table…

I’m using SQLCL here so we only see the first line of the error. However, we can use SQLCL’s OERR function to get the full error text, which is far more informative :

The error message makes it very clear what might be causing the error and gives you a good idea how to fix it.
Of course, you’ll always get this error if the synonym you’re referencing is pointing to a non-existent table, right ?

When Oracle starts yanking your chain

Let’s re-create the table in MIKE again …

create table predictions (
    predictor varchar2(128), 
    prediction varchar2(4000))
/

insert into predictions values('MY DAD', 'Liverpool will not win the League again in my lifetime')
/

grant select on predictions to hr
/

This time however, we’re going to create a public synonym (connecting as a user with CREATE PUBLIC SYNONYM privilege) …

create public synonym predictions for mike.predictions
/

…and drop HR’s local synonym…

drop synonym predictions;

…which leaves us with only the Public synonym pointing to the table…

HR can still access the table, but this time it’s using the public synonym…

Of course, if we drop the table again, we’ll get the same error when we attempt to access it via the synonym…

Wait, what ? What happened to that nice, explanatory ORA-00980 ?

OERR isn’t saying anything :


According to the docs :

Oracle Database attempts to resolve references to objects at the schema level before resolving them at the PUBLIC synonym level

In this instance, it seems that Oracle thinks that the synonym is pointing to itself.

We can confirm this hypothesis by using a public synonym with a different name to that of the table it’s pointing to.

First of all though, we need to drop our current public synonym or we’ll wind up right back here in looping chain lunacy :

drop public synonym predictions;

create table predictions (
    predictor varchar2(128), 
    prediction varchar2(4000))
/

insert into predictions values('MY DAD', 'Liverpool will not win the League again in my lifetime')
/

grant select on predictions to hr
/

create public synonym prophecies for mike.predictions;

HR now accesses the table using the PROPHECIES public synonym…


If we drop the underlying table again and attempt to use the synonym we’re back to a rather more reassuring error :

The moral of this tale ? Well, if you get an ORA-01775 error then it’s probably worth checking the availability of any tables that are a target of a public synonym as a first step to debugging the issue.

Oh, and my Dad knows best.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.