A synonym is an alias for a table, view, snapshot, sequence, procedure, function, or package.
There are two types to SYNONYMS they are
If you a create a synonym as public then it can be accessed by any other user with qualifying the synonym name i.e. the user doesn’t have to mention the owner name while accessing the synonym. Nevertheless the other user should have proper privilege to access the synonym. Private synonyms needs to be qualified with owner names.
To create a synonym for SCOTT emp table give the following command.
create synonym employee for scott.emp;
A synonym can be referenced in a DML statement the same way that the underlying object of the synonym can be referenced. For example, if a synonym named EMPLOYEE refers to a table or view, then the following statement is valid:
select * from employee;
Suppose you have created a function known as TODAY which returns the current date and time. Now you have granted execute permission on it to every other user of the database. Now these users can execute this function but when the call they have to give the following command:
select scott.today from dual;
Now if you create a public synonym on it then other users don’t have to qualify the function name with owner’s name. To define a public synonym give the following command.
create public synonym today for scott.today;
Now the other users can simply type the following command to access the function.
select today from dual;
To drop a synonym use the DROP SYNONYM statement. For example, to drop EMPLOYEE synonym give the statement
drop synonym employee;
To see synonyms information give the following statement.
select * from user_synonyms;