Home » SQL » How to Create Synonym in Oracle?

How to Create Synonym in Oracle?

By

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.

Syntax

Public Synonym

CREATE [OR REPLACE] [PUBLIC] SYNONYM synonym_name for [schema].object_name;

Private Synonym

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;

Output:

Synonym created.

Create a Private synonym for DEPT table in the SCOTT schema.

CREATE OR REPLACE SYNONYM s_dept FOR dept;

Output:

Synonym created.

Note: You should omit the PUBLIC clause from the CREATE SYNONYM Statement for the Private synonyms.

See also: