How to CREATE SYNONYM in Oracle?

 Introduction to Oracle CREATE SYNONYM statement

The CREATE SYNONYMthe statement allows you to create a synonym which is an alternative name for a database object such as a table, view, sequence, procedure, stored function, and materialized view.

Here is the basic syntax of creating a new synonym:

CREATE [OR REPLACE] [PUBLIC] SYNONYM schema.synonym_name
FOR schema.object;
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the synonym and its schema. If you skip the schema, Oracle will create the synonym in your own schema.
• Second, specify the object for which you want to create the synonym after the FOR keyword. Note that the schema object (schema.object) cannot be contained in a package.
• Third, use the OR REPLACE option if you want to re-create the synonym if it already exists. In case the synonym does not exist, the OR REPLACE has no effect.
• Fourth, use the PUBLIC keyword to create a public synonym which is a synonym that will be accessible to all users. Note that users must have sufficient privileges on the underlying objects to use the public synonyms.

Once you define a synonym for an object, you can reference it in the SQL statements such as the SELECT, INSERT, UPDATE, and DELETE statement.

Note that you can create a synonym for a table or a view that doesn’t exist. However, the target table or view must be available at the time you use the synonym. In addition, synonyms share the same namespace as tables or views, therefore, you cannot create a synonym which has the same name as a table or a view that already exists in the same schema.

Oracle CREATE SYNONYM example

This example uses the CREATE SYNONYM statement to create a synonym for the inventories table from the sample database:

CREATE SYNONYM stocks
FOR inventories;
Code language: SQL (Structured Query Language) (sql)

If you use SQL Developer, you can view the newly created synonym in under the Synonym nodes as shown in the following picture:

Now, you can use the stocks synonym instead of the inventories table in the query like the following:

SELECT * FROM stocks;
Code language: SQL (Structured Query Language) (sql)

Advantages of Oracle synonyms

First, synonyms allow you to change complicated and lengthy names by simplified aliases. It is very helpful if you work with the legacy systems. So instead of referring a table like human_resources.employee_locations, you can use offices.

Second, synonyms can help backward compatibility for the legacy applications. For example, you rename a table but do not want to affect the current applications that currently use the table. To keep the applications working properly, you can create a synonym that has the name the same as the old name of the table.

Third, synonyms help moving objects between schemas, even databases, without breaking the existing code.

In this tutorial, you have learned how to use the Oracle CREATE SYNONYM statement to create an alternative name for a database object.