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.
A synonym is an alias or friendly name for the database objects (such as tables, views, stored procedures, functions, and packages).
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With