Synonyms:

A synonym is a database object that is used as an alias name for any object. The major benefits of using Synonyms are:

Simplify SQL statements

Hide the real identity of an object

Use in database links

Synonyms can be private or public.  Synonyms created as a Public Synonym are accessible for all users. The Public synonyms are owned through the user group PUBLIC and can be dropped only through a DBA.

Syntax for creating a synonym name:

Syntax:

CREATE SYNONYM <synonymname> FOR <objectname>;

Example:

CREATE SYNONYM synemp FOR EMP;

SELECT * FROM synemp will display all the employee records in the emp table.

From now on, querying can be done using the synonym synemp. In addition using this synonym, DML operations can be performed. The Synonyms can be created for non-existing objects additionally. The problem comes only when querying is done on the synonym. That given example creates a synonym for a non-existing object.

CREATE SYNONYM s1 FOR TESTSS;

Synonym created.

SELECT * FROM s1; SELECT * FROM s1

* ERROR at line 1:

ORA-00980: the translation of synonym is no longer valid

Dropping Synonyms

Using the Drop command dropping of Synonyms can be done:

DROP SYNONYM synemp;