Re: Difference between oracle Synonyms and Views.

From: mcstock <mcstock_at_enquery.com>
Date: Tue, 28 Oct 2003 15:12:47 -0500
Message-ID: <xqGdnZQlSLZ8UAOiRVn-sw@comcast.com>

here's a flameless answer:

a synonym is a permanent alias. it can be public (available to all users) or private (owned by and used by one user, i.e., schema)

oracle's data dictionary views are accessed via public synonyms

for example, ALL_USERS is a public synonym for SYS.ALL_USERS

the synonym does not create a copy -- it simply provides a different, usually simpler name, typically masking the owner of the object

the synonym in itself does not imply access privileges on the object, these must be granted on the object as a separate action

a view is simple a stored SELECT statement -- literally, it is stored in the data dictionary. it can be used just about anywhere in a DML statement that an actual table name can be used -- i.e., the FROM clause of a SELECT statement, or in INSERT, UPDATE, and DELETE (with restrictions)

for example SYS.ALL_USERS is actually a view that select from a number of SYS tables, specifically:

    select u.name, u.user#, u.ctime

    from sys.user$ u, sys.ts$ dts, sys.ts$ tts
    where u.datats# = dts.ts#
      and u.tempts# = tts.ts#
      and u.type# = 1

views hide complexity, and also add a layer of security. if a user can have privileges on a view without having any privileges on the underlying tables.

do a select from the data dictionary view ALL_VIEWS to get an idea of the kind of complexity that views can hide.

So, neither synonyms nor views have any affect on data storage or require any storage or imply any additional copies of any data.

Please follow Dan's Suggestion and read up on these matters in the Oracle Docs. The SQL Reference Manual and Concepts guide are good places to start.

• MCS

"Simon Adebisi" <omar_adebisi_at_hotmail.com> wrote in message news:f1e58c26.0310281138.b5bc3be_at_posting.google.com...
> Prem:
>
> True to form, that wanker Dan Morgan doesn't know the answer and once
> again sends someone to an unsupported reference site.
>
> Quite simply:
>
> A Synonym creates a copy of the data but under a different name. You
> see, you don't want people having to remember difficult names. Plus it
> allows for data redundancy which cuts down on having to buy more kit.
> This may influence the backup schedule but, who knows, give 'er a try.
>
> A View, on the other hand, is sort of like a clustered index.
>
> Hope this helps mate. Glad to be back!
>
> Cheers,
>
> Simon Adebisi, OCP
> Sr. Oracle Architect
>
>
>
> Daniel Morgan <damorgan_at_x.washington.edu> wrote in message
news:<1067305536.430390_at_yasure>...
> > Prem wrote:
> >
> > >Hi oracle gurus,
> > >
> > >I am confused about the synonyms in Oracle. I just wanted to know the
> > >difference between Synonyms and views.
> > >Please help me in this matter.
> > >Thank you in advance.
> > >
> > >Prem Kalani.
> > >
> > >
> > The difference? There isn't even a relationship between them. You have
> > asked a question that
> > is roughly equivalent to what is the relationship between Mt. Everest
> > and a seagull.
> >
> > I would suggest you go to http://tahiti.oracle.com and look these two
> > items up.
> >
> > I could easily supply you with the answer. As could just about everyone
> > here. But quite
> > frankly I think it would be doing you a disservice. Get familiar with
> > tahiti and you will find
> > the answers to these and many more questions.
Received on Tue Oct 28 2003 - 14:12:47 CST