Press "Enter" to skip to content

How to Create Synonym in Oracle?

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:

Have you found the answer to your question? If not, you can discuss it with me in the comments section below or join my Q&A community OrclQA.com for developers and ask your question. It is FREE.

Vinish Kapoor

Follow

Hi, I am a full stack developer and writing about development. I document everything I learn and help thousands of people. foxinfotech.in is created, written, and maintained by me; it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub, and get notifications for new posts.

guest
0 Comments
Inline Feedbacks
View all comments