Oracle synonym management

Question

I read this article: Oracle Synonym Management

Regarding the order of preference, when it comes to resolving the name of the object to the actual object, he says:

• Local objects will be available first.

• If a local object does not exist, an object with a private synonym will be available.

• If the private synonym does not exist or the object does not exist, then the public synonym will be used.

I was wondering if there are no public facilities in this order?

eg. if the BOB user requests

select * from FOOBAR 

and there is no BOB.FOOBAR in dba_tables / views, but PUBLIC.FOOBAR.

Does Oracle solve its PUBLIC.FOOBAR or does it first check for synonyms?

Thanks.

Answer 1

At least up to 10 g PUBLIC is not a real user. You cannot create objects in the "General Scheme":

 SQL> CREATE TABLE public.foobar (id integer); CREATE TABLE public.foobar (id integer) ORA-00903: invalid table name SQL> CREATE TABLE system.foobar (id integer); Table created SQL> 

If you run this query:

 SELECT object_name FROM dba_objects WHERE owner='PUBLIC' AND object_type IN ('TABLE', 'VIEW'); 

You can answer the question about predefined tables / views in a publicly accessible “schema”.

Answer 2

In your example, FOOBAR almost certainly a synonym for the public. There is no PUBLIC scheme, but PUBLIC is listed as the owner of a public synonym.

If I create a new public synonym

 SQL> create public synonym pub_syn_emp 2 for scott.emp; Synonym created. 

the owner of this synonym ends with PUBLIC

 SQL> ed Wrote file afiedt.buf 1 select object_name, owner, object_type 2 from dba_objects 3* where object_name = 'PUB_SYN_EMP' SQL> / OBJECT_NAME OWNER OBJECT_TYP -------------------- ---------- ---------- PUB_SYN_EMP PUBLIC SYNONYM 

In addition, element number 3 does not look correct. If there is a private synonym pointing to a non-existent object and a public synonym pointing to a valid object, the private synonym still takes precedence. You simply get an error message when Oracle tries to resolve a private synonym for the actual object.

 SQL> create synonym syn_emp for scott.no_such_table; Synonym created. SQL> create public synonym syn_emp for scott.emp; Synonym created. SQL> select * from syn_emp; select * from syn_emp * ERROR at line 1: ORA-00980: synonym translation is no longer valid