All About Oracle Synonym

Synonym : A synonym is an alias for table, stored procedure and other database objects.

Main use of synonym :
1. Hide Complexity : If you have to access a object owned by another database user then
You have to use syntax Owner.Objectname every time to use that object. Instead of using cumbersome syntax every time we can create synonym for that
Create Synonym Syn_Name for Owner.Objectname
Suppose you have to access Table Tab_Y owner by user User_Y .Before creating synonym you have to use
Select * from User_Y.Tab_Y
Now if we create a synonym
Create Synonym Tab_Y for User_Y.Tab_Y
After creating synonym you can use
Select * from Tab_Y
Note : The object does not need to exist at the time of its creation

2 .Hide Owner/Location : In some applications application designers do not want to reveal the owner and location of object to other user. To achieve this they create synonym
Type of synonym : There are primarily two type of synonym
1. Private : Only owner of synonym can use private synonym By default synonym created is private
Create Synonym Tab_Y for User_Y.Tab_Y .
2. Public : Everyone can use public synonym. Syntax for creating public synonym
Create Public Synonym Tab_Y for User_Y.Tab_Y .

Order of precedence for synonym :
There are lot of confusion when both object and synonym exist in database, which one will be used first. Following is order of precedence
1. Local objects will always be accessed first.
2. If a local object does not exist, the object with a private synonym will be accessed.
3. If a private synonym does not exist or the object does not exist, then the public synonym will be used.

Flip Side of using synonym :
1. There are security issues related with public synonym
2. If there are lot of synonym in database then database performance will degrade i.e. suppose user want to query Tab_A in above mentioned example database parser have to go across large set of synonym and have to put each synonym in library cache along with dependency.

Alternative to using synonyms:
Use logon trigger to set ALTER SESSION SET CURRENT_SCHEMA This will make default schema for unqualified object
create or replace trigger trg_at_logon on database after logonbegin
if user in ('X','Y') then execute immediate 'alter session set current_schema=’DFLT’;
end if
end;
now if user X,Y login their default schema for unqualified object will be DFLT
Misc point about synonym :
We cannot use Drop and truncate command with synonym

Removing Synonyms :
We can use following syntax to drop private synonym
Drop synonym syn_name
To drop public synonnynm
Drop Public synonym syn_name
We can also use force command
Drop synonym syn_name force
The force syntax will force Oracle to drop the synonym even if it has dependencies. It is not a good idea to use the force phrase as it can cause invalidation of Oracle objects.

Query to find Synonym use in database :
Suppose we want to object that reference synonynm test_syn
Select owner, name, type
From dba_dependencies
Where referenced_name =
'TEST_SYN'
and referenced_type =
'SYNONYM'

Common Error with Synonym :
One of the most common error encountered with synonynm is
"ORA-00980: synonym translation is no longer valid"
Main causes for this to happen are
1. One has created a synonym on non-existing object by mistake.
2. You dropped an object but did not drop the synonyms which are referencing the object.

My Profile