Summary: in this tutorial, you will learn how to use the Oracle
CREATE SYNONYM statement to create an alternative name for a database object such as a table, view, sequence, procedure, stored function, and materialized view.
Introduction to Oracle
CREATE SYNONYM statement
Here is the basic syntax of creating a new synonym:
CREATE [OR REPLACE] [PUBLIC] SYNONYM schema.synonym_name FOR schema.object;
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
FORkeyword. Note that the schema object (
schema.object) cannot be contained in a package.
- Third, use the
OR REPLACEoption if you want to re-create the synonym if it already exists. In case the synonym does not exist, the
OR REPLACEhas no effect.
- Fourth, use the
PUBLICkeyword 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.
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;
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;
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
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.