Original subtitles

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.