In Oracle, Synonyms are alternative names for database objects and a synonym can be a public or private. A PUBLIC and able to be used by all users, and PRIVATE, that is, used by only the defining user. Private synonyms cannot have the same name as object’s name, but public synonyms can have the same name as object’s name.
You can create synonyms for database objects like table, view, sequence, procedure, stored function, package, materialized view, Java class schema object, user-defined object type, etc. The following are some examples with syntax.
CREATE [OR REPLACE] [PUBLIC] SYNONYM synonym_name for [schema].object_name;
CREATE [OR REPLACE] SYNONYM synonym_name for [schema].object_name;
Examples For Synonym
In the following case, we will create a public synonym for the EMP table in the SCOTT schema.
CREATE OR REPLACE PUBLIC SYNONYM emp FOR emp;
Create a Private synonym for DEPT table in the SCOTT schema.
CREATE OR REPLACE SYNONYM s_dept FOR dept;
Note: You should omit the PUBLIC clause from the CREATE SYNONYM Statement for the Private synonyms.