Explain SYNONYMS with example.

A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and database objects.

Syntax:

CREATE [OR REPLACE] [PUBLIC] SYNONYM [SCHEMA .] SYNONYM_NAME FOR [SCHEMA .] OBJECT_NAME [@ DBLINK];

In the syntax,

Ø    The OR replace phrase allows to recreate the synonym (if it already exists) without having to issue a DROP synonym command.

Ø    The PUBLIC phrase means that the synonym is a public synonym and is accessible to all users. Remember though that the user must first have the appropriate privileges to the object to use the synonym.

Ø    The SCHEMA phrase is the appropriate schema. If this phrase is omitted, Oracle assumes that a reference is made to the user's own schema.

Ø    The OBJECT_NAME phrase is the name of the object for which you are creating the synonym. It can be one of the following:

q  Table

q  Package

q  View

q  Sequence

q  Stored Procedure

q  User Defined Object

q  Function

q  Synonym

Example :

Create a synonym to a table named EMP held by the user SCOTT.

CREATE PUBLIC SYNONYM EMPLOYEES FOR SCOTT.EMP;

Output :

Synonym created.

Explanation:

Now, users of other schemas can reference the table EMP, which is now called as EMPLOYEES without having to prefix the table name with the schema named SCOTT. For example:

SELECT * FROM EMPLOYEES;

DROPPING SYNONYMS

Syntax:

DROP [PUBLIC] SYNONYM [SCHEMA .] SYNONYM_NAME [FORCE];

In the syntax,

Ø    The PUBLIC phrase allows to drop a public synonym. If public is specified, then there is no need to specify a schema.

Ø    The FORCE phrase will force Oracle to drop the synonym even if it has dependencies. It is probably not a good idea to use the force phrase as it can cause invalidation of Oracle objects.

Example:

Drop the public synonym named EMPLOYEES

DROP PUBLIC SYNONYM EMPLOYEES;