can we create synonym with the same name in same schema

Question

I'm preparing for SQL Expert certification, and I found one question and It said,which is the correct option. And, out of four, one option said A table and a synonym can have the same name in the same schema.

And, per my knowledge, in oracle anything we create that treated as an object, which means when we say create synonym, which means we are creating new object. And create same object in same schema not allowed in Oracle or any database AFAIK.

Even Burleson says

You can have a public and private synonym of the same name. In fact, you can have a public and private synonym called EMP in the SCOTT schema and have a table called EMP in the same schema

So, I tried.

create synonym emp for scott.emp

It shows some error object already exist

Then I tried

create public synonym emp for scott.emp. 

And, got same error. So, anyone please share some knowledge on Synonyms. Can we create synonyms with same name in same schema ?

Solution

You can have:

• Table and Public Synonym with the same name
• Public Synonym and Private Synonym with the same name

But can not have:

• Table and Private Synonym with the same name inside the same schema

The first thing to note is that Public Synonyms are non-schema objects, while Private Synonyms and Tables are. Another is that the uniqueness of database objects' names are defined by the namespace. As it is stated in SQL Expert Study Guide:

• USER, ROLE, and PUBLIC SYNONYM objects are in their own collective namespace.
• TABLE, VIEW, SEQUENCE, PRIVATE SYNONYM, and user-defined TYPE objects have their own unique namespace within a given schema.
• INDEX objects have their own namespace within a given schema.
• CONSTRAINT objects have their own namespace within a given schema.

So, as long as objects do not share the same namespace you can give them the same names. Hope this helps.

Comments

[Milaci]

Which is the result if i do a select on the table that have also a Public Synonym with the same name? Table result or Public Synonym table result?

[Pham X. Bach]

@Milaci I had tested, can only select table result, not Public Synonym table result.