Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Difference between Synonym and View

Posted on 2002-06-05
5
Medium Priority
?
1,323 Views
Last Modified: 2012-06-22
I've created a View X as follows :

CREATE VIEW X AS SELECT * FROM EMP;

as well i've created a SYNONYM Y on Table EMP

What are the difference between the two(X & Y) in terms of operations that can/cannot be performed, performance, internal representation etc.
 
0
Comment
Question by:send2sachin
5 Comments
 
LVL 2

Assisted Solution

by:dbaora
dbaora earned 100 total points
ID: 7058520
Hi send2sachin,
a view is a look on a table's data with restrictions or specific data - a subset of the table's data.
a synonym is a way to share any object from your
scheme with other schemes.
a synonym is created to avoid using the user.object standard while calling to other scheme objects.
any privilege you have on the object, will affect the synonymm if you grant select only - no insert or update is allowed using synonym (a synonym is a pointer to an object).

example:
say you want to share scott's emp table, you simply grant select on the table to other users, than those users can relate to the emp table using SCOTT.EMP. to avoid that simply create a public synonym called emp for SCOTT.EMP.
now every user that will select from EMP will get data from SCOTT.EMP.

regards,
dbaora.
0
 

Author Comment

by:send2sachin
ID: 7060984
I'll repeat my question one again ....

what exactly i am expecting is consider the following two SQL statements

1: CREATE VIEW X  AS SELECT * FROM EMP;
2: CREATE SYNONYM Y FOR EMP;

now can anyone tell me what all operations can be performed on X which can't be performed on Y and Vice versa ? How are X and Y represented by oracle internally ?
0
 
LVL 4

Accepted Solution

by:
asimkovsky earned 100 total points
ID: 7062181
** How are X and Y represented by oracle internally ?

This question was answered by dbaora.



**what all operations can be performed on X which can't be performed on Y and Vice versa ?

A view is just a stored query.  So any user with the appropriate privileges can query, insert, update, delete the data.  The synonym is a pointer to the base table itslef.  So any user with the appropriate privileges can query, insert, update, delete the data, and also perform DDL on the base table, such as adding/dropping columns. Of course, all that can be controlled by properly managing privleges anyway.

The one you choose to use will depend on the requirements of your system.  Perhaps you can expand on that a little bit and somebody can give you an answer closer to what you're looking for?


Andrew

0
 

Expert Comment

by:modulo
ID: 7429526
Dear: dbaora

I've rejected your proposed answer as Experts Exchange holds an experiment to work without the answer button.

See:        http://www.experts-exchange.com/jsp/communityNews.jsp
Paragraph: Site Update for Wednesday, November 06, 2002

By this rejection the Asker will be notified by mail and hopefully he will take his responsibility to finalize the question or post an additional comment.
The Asker sees a button beside every post which says "Accept This Comment As Answer" (including rejected answers) -- so if he/she thinks yours is the best, you'll be awarded the points and the grade.

Thanks !

modulo

Community Support Moderator
Experts Exchange
0
 
LVL 5

Expert Comment

by:jpkemp
ID: 9025074
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Split between dbaora & asimkovsky
Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
jpkemp
EE Cleanup Volunteer
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question