What is Synonyms and types of Synonyms ?

Synonyms

This section introduces you to Oracle synonyms that help you create aliases for schema objects such as tables, views, materialized views, sequences, procedures, and stored function.

Synonyms provide a level of security by hiding the name and owner of a schema object such as a table or a view. On top of that, they provide location transparency for remote objects of a distributed database.

Synonyms as the word literally means, are aliases for referencing the database objects. By default, when you look for an object in the database, it tries to search it in your own schema. So, if you are user user01 and you try to query an object emp from scott’s schema, oracle will return an error :

sql> select * from emp
select * from emp
              *
ERROR at line 1:
ORA-00942: table or view does not exist

Why do we need synonyms?

1. Synonyms make the referencing of objects easier. As in above example, an object like scott.emp@dblink on remote database can be referenced as just emp on a local database using synonym.
2. It eliminates the hard coding. For e.g., if a table emp is moved from scott schema to hrm schema and the PL/SQL code uses the hard coded table name scott.emp several times in the code. This creates an issues for the programmer to replace multiple references of the table in PL/SQL code. With use of synonyms in PL/SQL code though, we can just update the synonym only to make the code legit again. Thus node code changes.
3. It also hides object details like object name, owner, database link.

Types of synonyms

There are 2 basic categories of synonyms :
1. Public synonyms
2. Private synonyms

Public synonym	Private synonym
Can be accessed by any Database user. Individual user who creates a public synonym, does not own it. Rather, it belongs to PUBLIC user group.	Database user creating the synonym, owns it and thus the only one who can access it

Syntax

The syntax to create a private synonym is :

sql> CREATE SYNONYM synonym_name FOR object_name;

The syntax to create a public synonym is :

sql> CREATE PUBLIC SYNONYM synonym_name FOR object_name;

Example : Private Synonym

sql> conn scott/tiger
sql> CREATE SYNONYM emp FOR emp;

Example : Public Synonym

sql> conn hrm/password
sql> CREATE SYNONYM emp FOR scott.emp;

Order of precedence

It is very important to understand the order of precedence when you have public, private and a local database object with the same name. Yes, you can have all of them with the same name, in same database. Thus the order of precedence in this case would be :
1. local object
2. private synonym
3. public synonym

Dropping synonyms

The drop synonym command can be used to drop the synonyms. Remember to use PUBLIC clause while dropping a public synonym.
1. Drop public synonym

SQL> DROP PUBLIC SYNONYM emp;

2. Drop private synonym

SQL> DROP SYNONYM emp;