Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Managing Oracle Synonyms

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:

  1. Local objects will always be accessed first.

  2. If a local object does not exist, the object with a private synonym will be accessed.

  3. 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.

like image 459
Will Avatar asked Jul 13 '11 14:07

Will


People also ask

What is a synonym in Oracle?

A synonym is an alias or friendly name for the database objects (such as tables, views, stored procedures, functions, and packages).

What is public synonym in Oracle?

Public synonyms are accessible to all users. Oracle uses a public synonym only when resolving references to an object if the object is not prefaced by a schema and the object is not followed by a database link. If you omit this clause, the synonym is private and is accessible only within its schema.

What is private synonym in Oracle?

When resolving references to an object, Oracle Database uses a public synonym only if the object is not prefaced by a schema and is not followed by a database link. If you omit this clause, then the synonym is private and is accessible only within its schema. A private synonym name must be unique in its schema.


1 Answers

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
like image 74
Justin Cave Avatar answered Oct 06 '22 06:10

Justin Cave