Managing Oracle Synonyms

Question

I was reading this article: Managing Oracle Synonyms

Regarding the order of preference, when it come to resolving an object name to the actual object, it says:

Local objects will always be accessed first.
If a local object does not exist, the object with a private synonym will be accessed.
If a private synonym does not exist or the object does not exist, then the public synonym will be used.

I was wondering if the public objects are missing in this order somehow?

E.g. if user BOB queries

select * from FOOBAR

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

Does Oracle resolve it to PUBLIC.FOOBAR or will it check for synonyms first?

Thank you.

score 10 · Answer 1 · answered Jul 13 '11 at 14:36

In your example, FOOBAR is almost certainly a public synonym. There is no PUBLIC schema 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 that synonym ends up being 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, item #3 does not appear to be correct. If there is a private synonym that points to a non-existent object and a public synonym that points to a valid object, the private synonym still takes precedence. You'll just get an error when Oracle tries to resolve the private synonym to an 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

Oh, so it's just synonyms that can have PUBLIC as a owner? As it seems that you cannot create any tables/views with a public owner, my question would be whether there are any predefined tables or views that have PUBLIC as a owner. — Will, Jul 14 '11 at 10:56
@Will - There are no predefined tables or views that are owned by the pseudo-schema `PUBLIC`. There is a pseudo-role `PUBLIC` that is implicitly granted to all users that has some privileges granted to it mainly to grant execute access on various predefined packages to all users. — Justin Cave, Jul 14 '11 at 14:11

DCookie · Accepted Answer · 2011-07-14T13:51:31.643

3

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

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 pre-defined tables/views in the PUBLIC "schema".

edited Jul 14 '11 at 13:51

answered Jul 13 '11 at 14:39

DCookie

42,630
11
83
92

Well, you can create public synonyms, which are objects as well. I would be interested in: 1) can you create tables/views in the public schema? 2) are there any predefined tables/views in the public schema? – Will Jul 14 '11 at 10:53
That's what I'm showing above. You cannot create a table in the public "schema". – DCookie Jul 14 '11 at 13:46
See my updated answer where I address the predefined question. – DCookie Jul 14 '11 at 13:52

Managing Oracle Synonyms

2 Answers2