Creating and Using Synonym in Oracle Database
Synonyms are used to provide duplicate names to existing objects in a database. It neither holds any data nor the structure of an object. It is just merely a named reference of a database object.
Synonym can be of two types:
- Private Synonyms: These are those synonyms that can be used only by the user who created it.
- Public Synonym: These are those synonyms which once created can be used by all users.
Creating a Synonym
Syntax: CREATE [PUBLIC] SYNONYM <SYNONYM_NAME> FOR <OBJECT_NAME>;
The example shown below would create a private synonym named 'SEMP' for the table EMPLOYEE.
Example: CREATE SYNONYM SEMP FOR EMPLOYEE;
To create a public synonym we can use the public option as shown below:
Example: CREATE PUBLIC SYNONYM SEMP FOR EMPLOYEE;
Once a synonym is created for an object, we can use the synonym for any operation to be performed on the object. The examples below show how to use the synonym SEMP we just created for EMPLOYEE.
Example: INSERT INTO SEMP(EMPNO, NAME) VALUES(1, 'RICK'); SELECT * FROM SEMP;
Notice the use of the synonym (SEMP) in place of the table EMPLOYEE.
Deleting a Synonym
We can use the DROP statement to delete a synonym as shown below:
Example: DROP SYNONYM SEMP;