How to correctly make a public synonym

I think Justin is on the right track. What I think it actually means is that mydbowner.mytable doesn't exist.

Here's an example:

SQL> conn mbobakEnter password: Connected.SQL> drop table mytable;drop table mytable           *ERROR at line 1:ORA-00942: table or view does not existSQL> create public synonym mytable for mbobak.mytable;Synonym created.SQL> select * from mytable;select * from mytable              *ERROR at line 1:ORA-01775: looping chain of synonyms

I think what's happening is that Oracle tries to resolve mytable, there is no mytable in mbobak schema, so it looks for it in PUBLIC, it finds it, and sees that it points to mbobak.mytable. But, mbobak.mytable doesn't exist, so, it looks for mytable in PUBLIC, and there's the loop.

And in fact, if you create mytable, the error goes away:

SQL> create table mytable as select * from dual;Table created.SQL> select * from mytable;D-X1 row selected.SQL> drop table mytable;Table dropped.SQL> select * from mytable;select * from mytable              *ERROR at line 1:ORA-01775: looping chain of synonyms

Yes, I realize that doesn't really entirely make sense, as, once the public synonym resolved to mbobak.mytable, and that's not found, it seems to me, it should return an error ORA-942 "table or view does not exist", which makes far more sense to me.

But, this does seem to be how it works.

QED

Hope that helps.

The error you're getting implies that mydbowner.mytable is not, in fact a table. What does

SELECT object_type  FROM all_objects WHERE owner = 'MYDBOWNER'   AND object_name = 'MYTABLE'

return?