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;
Database views related to synonyms
The dynamic views like dba_synonyms and user_synonyms can be used to query the details of a synonym.
SQL> SELECT synonym_name, table_owner, table_name FROM DBA_SYNONYMS where table_name='EMP'; SYNONYM_NAME TABLE_OWNER TABLE_NAME ------------------------------ ------------------------------ ------------------------------ EMP SCOTT EMP