Synonyms in Oracle with Examples

Back to: Oracle Tutorials for Beginners and Professionals

Synonyms in Oracle with Examples

In this article, I am going to discuss Synonyms in Oracle with Examples. Please read our previous article where we discussed the basic concepts of Examples on Sequences in Oracle.

What are Synonyms in Oracle?

Synonyms are the alternate name of the objects. The synonym can be created to give an alternative name to a table or to another database object. This requires no storage other than its definition in the data dictionary. This is useful for hiding the identity and location of an underlying schema object. We have two types of synonyms.

• Public Synonyms
• Private Synonyms

Creation of Synonyms in Oracle:

Simplify access to objects by creating a synonym (another name for an object). We can create an easier reference to a table that is owned by another user. We can shorten the lengthy object names.

Syntax: CREATE [PUBLIC] SYNONYM SYNONYM_NAME for object;

To refer to a table that is owned by another user, you need to prefix the table name with the name of the user who created it, followed by a period. Creating a synonym eliminates the need to qualify the object name with the schema and provides you with an alternative name for a table, view, sequence, procedure, or other objects. This method can be especially useful with lengthy object names, such as views.

In the syntax:

1. PUBLIC – Create a synonym that is accessible to all users
2. SYNONYM- It is the name of the synonyms to be created.
3. OBJECT- Identifies the object for which the synonym is created.

Guidelines to Follow when Working with Synonyms in Oracle:

• The object cannot be contained in a package.
• A private synonym name must be distinct from all the other objects that are owned by the same user.
• To create a PUBLIC synonym, you must have the CREATE PUBLIC SYNONYM system privilege.

Examples to Understand Synonyms in Oracle:

We have a table called EMPLOYEES. We have to create an alternative name for the table EMPLOYEES.

CREATE SYNONYM E FOR EMPLOYEES;

Let’s go ahead and create the synonym.

So, the synonym is created. Now, we can query the E synonym just as same as the employees.

select * from E;

We are querying the Employees table with the name of synonym E. We can even check the synonym information from the dictionary table called USER_SYNONYMS.

select * from USER_SYNONYMS;

We can see the synonym details with synonym name, table_owner, and table_name. We can drop the synonym by using the command “ drop synonym synonym_name”.

DROP SYNONYM E;

Let’s assume that there are two users hr and manager. The user hr can query the tables in this schema without mentioning the owner name in the query.

For example, there is a table called emp in the hr schema. The user hr can query without mentioning the schema name in the query like “ select * from emp”. There is no need of mentioning “select * from hr.emp”.

The user manager doesn’t have any tables in the schema but he has access to the tables in hr user. In that case, if any user needs to access another user table, then we must mention the username prefix in the query.

Select * from hr.emp;

In order to avoid mentioning the username every time then the hr user can create a public synonym.

Create public synonym emp for hr.emp;

So, once this synonym is created, then the user manager can easily access the table emp using the synonym.

select * from emp;

Here the user manager is accessing synonym emp. Let’s try to create a public synonym for the table employees.

CREATE PUBLIC SYNONYM EMPLOYEES FOR HR.EMPLOYEES;

In order to create a public synonym, the user needs a privilege called the ‘CREATE PUBLIC SYNONYM’ privilege.

In the next article, I am going to discuss Indexes in Oracle with Examples. Here, in this article, I try to explain Synonyms in Oracle with Examples. I hope you enjoy this Synonyms in Oracle article.