Synonyms in Oracle

What are synonyms ?

  • They are auxiliary names that relate to other database objects: tables, procedures, views, etc.
  • They work like Unix hard links; a pointer to point to an object that exists somewhere else.
  • Synonyms can be created as PRIVATE (by default) or PUBLIC.
  • public synonyms are available to all users in the database.
  • private synonyms exist only in specific user schema (they are available only to a user and to grantees for the underlying object)

When to Use Synonyms ?

  • As database systems grow and applications improve, there is usually a need to change the names of tables and views to better reflect their new functionality. 

A synonym can be created on the following types of objects:

  • table
  • view
  • stored procedure
  • function
  • package
  • sequence
  • materialised view
  • synonym
  • java class schema object
  • user-defined object

Syntax :

CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema.] synonym_name FOR [schema.] object_name [@dblink_name];

Example :

CREATE SYNONYM emp FOR hr.employees;

Reference :

Leave a Comment