What is a SYNONYM?


Synonym is an alternative name for a table, view, sequence, procedure, stored function, package, materialized view, Java class schema object, user-defined object type, or another synonym.

Synonyms provide both data independence and location transparency. Synonyms permit applications to function without modification regardless of which user owns the table or view. However, synonyms are not a substitute for privileges on database objects. Appropriate privileges must be granted to a user before the user can use the synonym.

To create a private synonym in your own schema, you must have the CREATE SYNONYM system privilege.
To create a private synonym in another user’s schema, you must have the CREATE ANY SYNONYM system privilege.
To create a PUBLIC synonym, you must have the CREATE PUBLIC SYNONYM system privilege.

What order does synonym execute in Oracle DB?
Local Objects –> Private Synonyms –> Public Synonyms

SQL> create or replace synonym employee for scott.emp; — private synonym
Synonym created.

SQL> create or replace public synonym employee for scott.emp; — public synonym
Synonym created.

 You can have public and private synonym with same name. However you need to be careful while dropping it.

SQL> drop synonym employee; — dropping synonym
Synonym dropped.

SQL> drop public synonym employee; — dropping public synonym
Synonym dropped.

Local Objects should  have unique name.
Public Object should be unique as well.
Be careful while using REPLACE function with PUBLIC synonym or any other objects.
Never use “REPALCE” unless you are 2500 %  certain that you don't want the existing object replaced. I have been the "OR REPLACE" guilty myself.



Interested in working with me? I can be reached at pbaniya04[at] for any questions, consulting opportunities or you may drop a line to say HELLO. Thank your again for visiting my blog and looking forward to serving you more.

Have a Database-ious Day!

No comments

Powered by Blogger.