All language subtitles for [SubtitleTools.com] Synonyms - Learning Oracle 12c [Video]

In this lesson, we'll
be looking at synonyms.

Now a synonym in
the Oracle database

is an object that
allows for basically

an alias to another database
object name, usually a table.

And that's to avoid schema
notations or table-dot

notations that we
sometimes refer to.

So we'll look at this example in
a moment, but to describe this,

if the user Kara logs in to
the database and wants to do

a select from the table
called emp in Scott's schema,

she can't simply do select* from
emp because emp is not in her

schema, it's in Scott's schema.

So she has to do a
select* from scott.emp.

And obviously, in
an organization

with lots and lots of tables
with many different schemas,

it's considered by some
to be problematic to have

to notate a table that way
every time, to always have

to use the table notation where
we put the schema dot and then

the table name.

So that's considered by some
to be extra code, more work,

and those kind of thing.

So a synonym allows for us to
create an alias for the table

so we don't have to use the
schema-dot-table notation.

There are two different
kinds of synonyms.

There's a private synonym,
and a private synonym

is owned by the user.

So in our case,
Kara user would own

a private synonym
that was an alias that

pointed to another table.

But we also have
public synonyms.

And public synonyms are
available to every user.

So you could think of
them as a global alias

for a particular table.

And sometimes
public synonyms are

used on a table that's very
common to all applications,

so some sort of lookup table
or a table with company

information that belong to all
the different applications that

are supported by the database.

So private synonyms
and public synonyms.

A couple of words of
warning about synonyms.

Too many synonyms
in a database can

be really confusing as
far as what is pointing

to what in terms of aliases.

So that's something that has
to be taken into consideration.

If you use a synonym-based
strategy in your database,

it has to be fairly
thought out and defined

before you jump into that.

The other thing that
should be considered

is that coding
standards may actually

require schema notation,
and for very good reasons

because, when you look at a
piece of code that's selecting

from tables in
the database, it's

very helpful to see that the
schema-dot-table notation is

there, so you know exactly what
owner owns the object that's

being referenced in the code.

So some coding
standards actually

require you to use
schema notation

and certainly wouldn't
get any argument from me.

But synonyms can be used
as aliases in situations

where that is advantageous.

So let's connect up
to our Kara user.

And just to prove
this out a little bit,

let's try select star from emp.

Of course, we get
table or view does not

exist because Kara owns
no tables named emp.

What we're wanting to
reference is the emp table

that's in the Scott schema.

So we say select*
from scott.emp.

And then we see the data.

So let's attempt to create
a synonym in Kara's schema

that points to the
scott.emp table.

So we say, create synonym
emp for scott.emp.

Now we get an error, an
insufficient privileges

error in this case.

And I wanted to show
this to you just

to make the point that
creating a synonym

is creating a database object.

And as such, it has certain
permissions that go with it,

so system privileges
that are required.

So I'm going to pull up command
prompt, and we'll SQL plus.

And we will grant
create synonym to Kara.

I'm going to clear this and
have Kara create that synonym.

So that is a private synonym.

That synonym exists
in Kara's schema.

What about a public synonym?

So let's have Kara
create a public synonym

for the DEPT table
in the Scott schema.

And that will make it available
in terms of that alias

to all users.

And again, we get an
insufficient privileges there.

So creating a private synonym
is a different privilege

than creating a public synonym.

So we need to grant create
public synonym to Kara as well.

So now Kara has
a private synonym

that refers to the scott.emp
table and a public synonym that

refers to scott.dept. So whereas
before we couldn't do this,

now we can because the private
synonym emp refers back

to the scott.emp table because
a private synonym exists for it,

and then the same would be
for the dept table as well.