Lesson 22 : Synonym and public synonym

A synonym is an alternative name for any existing  objects such as tables, views, sequences,stored functions, stored procedures, packages and other existing database objects.

Syntax :

CREATE SYNONYM <synonym name> FOR schema.objectname ;

Example :

SQL> CREATE SYNONYM emp FOR  scott.employee_master ;

With REPLACE option

Syntax :

CREATE OR REPLACE SYNONYM <synonym name> FOR schema.objectname ;

REPLACE option allows you to recreate the existing synonym  without using DROP synonym command.

Example :

SQL> CREATE  OR  REPLACE SYNONYM emp FOR  scott.employee_master ;

PUBLIC SYNONYM

Public synonyms are available to all users or schemas in the database.

Syntax :

CREATE OR REPLACE PUBLIC SYNONYM <synonym name> FOR schema.objectname ;

Example :

SQL> create public synonym compute_tax for emp.tax_calculation;

Here, compute_tax is synonym for the procedure tax_calculation which is available under emp schema.

DROP SYNONYM

To elimanate existing synonym from database.

Syntax :

DROP SYNONYM <synonym name>;

Example :

SQL> drop synonym compute_tax ;

Note : It just drops synonym but not the referenced object.