Oracle SQL Synonyms

Posted by : Akshay Patil Thursday, 16 April 2015

What are Synonyms?

Synonyms are aliases for tables, views, sequences.
Simplify access to objects by creating a synonym (another name for an object).
Shorten lengthy object names.
Creating a Synonym for an Object
To refer to a table owned by another user, you need to prefix the table name with the name of the user who created it followed by a period. Creating a synonym eliminates the need to qualify the object name with the schema and provides you with an alternative name for a table, view, sequence, procedure, or other objects. This method can be especially useful with lengthy object names, such as views.

There are two categories of synonyms: PUBLIC and PRIVATE :

A PUBLIC synonym can be accessed by any system user.
The individual creating a PUBLIC synonym does not own the synonym.
Rather, it will belong to the PUBLIC user group that exists.
PRIVATE synonyms, on the other hand, belong to the system user that creates them and reside in that user's schema.
A system user can grant the privilege to use private synonyms that they own to other system users.
In order to create synonyms, we need to have the CREATE SYNONYM privilege.
We must have the CREATE PUBLIC SYNONYM privilege in order to create public synonyms.

Guidelines :

The object cannot be contained in a package.
A private synonym name must be distinct from all other objects owned by the same user.

Creating Synonyms :
Syntax :

CREATE [PUBLIC] SYNONYM synonym_nameFOR object_name;

In the syntax:

PUBLIC :- creates a synonym accessible to all users
synonym_name :- is the name of the synonym to be created
object_name :- identifies the object for which the synonym is created

Create a shortened name for the SALES_ORDER table.

CREATE SYNONYM SYN_SOFOR SALES_ORDER;/*Synonym Created.*/

Removing Synonyms :

Drop a synonym.

DROP SYNONYM SYN_SO;/*Synonym dropped.*/

A PRIVATE synonym can be deleted by its owner.
A PUBLIC synonym can only be deleted by a user with DBA privileges.
In order to drop a public synonym you must include the PUBLIC keyword in the DROP SYNONYM command.
In order to drop a public synonym, you must have the DROP PUBLIC SYNONYM privilege.

DROP PUBLIC SYNONYM synonym_name;/*Synonym dropped.*/

Renaming Synonyms :

Private synonyms can be renamed with the RENAME SYNONYM command.
All existing references to the synonym are automatically updated.
Any system user with privileges to use a synonym will retain those privileges if the synonym name is changed.

Syntax :

RENAME old_synonym_name TO new_synonym_name;/*Synonym renamed.*/

The RENAME SYNONYM command only works for private synonyms.
If we attempt to rename a public synonym such as the TBLSPACES synonym, Oracle will return an ORA-04043: object TBLSPACES does not exist error message as is shown here.

RENAME TBLSPACES TO TS;Error report:SQL Error: ORA-04043: object TBLSPACES does not exist04043. 00000 - "object %s does not exist"

Advantages of Synonyms

A synonym provides what is termed location transparency because the synonym name hides the actual object name and object owner from the user of the synonym.
A public synonym can be used to allow easy access to an object for all system users.
The binding between a synonym and its base object is by name only. All existence, type, and permissions checking on the base object is deferred until run time.
Therefore, the base object can be modified, dropped, or dropped and replaced by another object that has the same name as the original base object.

To be removed

For example, consider a synonym, MyContacts, that references the Person.Contact table in Adventure Works. If the Contact table is dropped and replaced by a view named Person.Contact, MyContacts now references the Person.Contact view.

Disadvantages of Synonyms :

There is no ALTER SYNONYM statement, we first have to drop the synonym, then re-create the synonym with the same name.
SYNONYMs are loosely bound to the referenced objects. So you can delete a SYNONYM without getting any warning that it is being referenced by any other database object.