Difference between View and Synonym in Oracle database

It is one of commonly asked interview question from database section. View and Synonym is common concept relevant for all database however, the syntax and semantics discussed in this post is oracle database specific.

View is a virtual entity based on table or other views.View is created using database tables(primary storage of data in relational database) or other views.In other words, view is just a stored SELECT statement (a logical table) based on one or multiple tables or views and view does not have its own data, it relies on underlying base tables for result sets.
Note:- Along with table based views, oracle database also support object view or a relational view that supports LOBs, object types, REF datatypes, nested table, varray. XML view can also be created supporting XML types.

Synonym is alternate name for database objects like Table, views, procedure, function , etc. Synonym provides an alias to a database object and gives an opportunity to mask original name & owner of the object.
Synonym is mostly used for providing access on table to external schema by creating Public synonym and by doing this external schema can access table without specifying schema_name where table is existing.

Syntax & semantics of View and Synonym creation:- 

View creation :- General syntax for table/view based view

CREATE [ OR REPLACE ] [ [ NO ] FORCE ] VIEW
   [ schema. ]view
   [ (alias [ inline_constraint
              [ inline_constraint ]... ]
     | out_of_line_constraint
       [, alias [ inline_constraint
                  [ inline_constraint ]... ]
       | out_of_line_constraint
       ]...
     )
   | object_view_clause
   | XMLType_view_clause
   ]
   AS subquery [ subquery_restriction_clause ] ;

For creating View in our schema we must have CREATE VIEW and for creating view in anothers schema we must have CREATE ANY VIEW system privilege.
Let's create a view based on multiple table in HR schema - Employees, Departments and Jobs
View can be created using following syntax:-

CREATE VIEW EMPLOYEE_DETAILS
AS 
SELECT emp.FIRST_NAME, emp.EMAIL,dept.DEPARTMENT_NAME, job.JOB_TITLE
FROM 
EMPLOYEES emp, DEPARTMENTS dept ,JOBS job 
WHERE 
emp.DEPARTMENT_ID = dept.DEPARTMENT_ID
AND job.JOB_ID = emp.JOB_ID;

when we execute above query it will create a view object "EMPLOYEE_DETAILS" in database and it can be queried as a table like this.

Select * from  EMPLOYEE_DETAILS;

Other clauses supported in View are - OR REPLACE, FORCE, READ ONLY,CHECK OPTION.
1.OR REPLACE :- If appended, it will recreate view and it is used to alter definition of an existing view without doing rework (dropping, applying grants again, etc).

CREATE OR REPLACE VIEW EMPLOYEE_DETAILS
AS 
SELECT 
....
FROM
......

2.FORCE:- If appended, it will forcefully creates view even if base tables or view does not exist or grants to accessing object is not there.
Note:- Before executing any select, insert,update query above structure should be in place,otherwise it will throe error.

CREATE OR REPLACE FORCE VIEW EMPLOYEE_DETAILS
AS 
SELECT 
....
FROM
......

3. READ ONLY:- View created only used for select operation. No insert ,update are allowed on read only views.

CREATE OR REPLACE FORCE VIEW EMPLOYEE_DETAILS
AS SELECT emp.FIRST_NAME, emp.EMAIL,dept.DEPARTMENT_NAME, job.JOB_TITLE
FROM 
EMPLOYEES emp, DEPARTMENTS dept ,JOBS job 
WHERE 
emp.DEPARTMENT_ID = dept.DEPARTMENT_ID
AND job.JOB_ID = emp.JOB_ID WITH READ ONLY;

4. CHECK OPTION:- If check option is enabled, insert or update can be executed but with some restriction.

CREATE OR REPLACE FORCE VIEW EMPLOYEE_DETAILS
AS SELECT emp.FIRST_NAME, emp.EMAIL,dept.DEPARTMENT_NAME, job.JOB_TITLE
FROM 
EMPLOYEES emp, DEPARTMENTS dept ,JOBS job 
WHERE 
emp.DEPARTMENT_ID = dept.DEPARTMENT_ID
AND job.JOB_ID = emp.JOB_ID WITH CHECK OPTION;

Note:- Query should not contain Group By Clause, Group Function ,Distinct Clause,References to any expression,Refrences to any pseudo column rownum. For more detail refer official guide. Read What is difference between view and materialized view ?

Synonym creation :- Synonym is created for an database object to hide details of object and it provides  data independence and location transparency too.
Synonym can be of two types: private (default) and public.
For creating private synonym in our own schema we need CREATE SYNONYM privilege and for creating in others schema we need CREATE ANY SYNONYM privilege.For public synonym we need CREATE PUBLIC SYNONYM privilege.

CREATE [ OR REPLACE ] [ PUBLIC ] SYNONYM
   [ schema. ]synonym 
   FOR [ schema. ]object [ @ dblink ] ;

Lets create a synonym for stored procedure and execute it. Use following syntax for creating procedure and synonym:
Create procedure get_employee_names:- 

create or replace procedure get_employee_names(v_deptId_in NUMBER, v_cur OUT SYS_REFCURSOR ) IS
begin
  open v_cur for select FIRST_NAME,LAST_NAME from employees where DEPARTMENT_ID = v_deptId_in;
end get_employee_names;
/

Create a synonym for above stored procedure:-

--GET_EMPLOYEE_NAMES(In NUMBER, OUT REF CURSOR) is a procedure
 CREATE OR REPLACE SYNONYM "HR"."MY_PROCEDURE_SYNONYM" FOR "HR"."GET_EMPLOYEE_NAMES";

Now we can execute the above stored procedure via synonym with appropriate parameter passing. Refer the following diagram and execute command in following order :-

We can see that, we have not executed original stored procedure instead we executed MY_PROCUDURE_SYNONYM. Similarly, synonym for table and other objects.

Advantage of Views and Synonyms:- 
With views: we can hide columns , add predicates (WHERE clause) to restrict rows, rename columns, give a column name to a SQL expression.
With synonym: we can reference objects in other schemas(using public synonym) and databases without qualifying the name.

======End of post======

Read also:
1. How to test stored procedure in SQL*PLUS and SQL developer
2. Internals of PL/SQL execution in database server