Wednesday, October 15, 2014

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;

No comments: