oracle 我们可以在同一个模式中创建同名的同义词吗

can we create synonym with the same name in same schema

提问by Ravi

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.

我正在准备 SQL Expert 认证，我发现了一个问题，它说，这是正确的选项。而且，在四个选项中，一个选项说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.

而且，据我所知，在 oracle 中，我们创建的任何东西都被视为对象，这意味着当我们说 时create synonym，这意味着我们正在创建新对象。并在 Oracle 或任何数据库 AFAIK 中不允许的相同模式中创建相同的对象。

Even Burlesonsays

甚至Burleson说

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

您可以拥有同名的公共和私人同义词。事实上，您可以在 SCOTT 模式中拥有一个名为 EMP 的公共和私有同义词，并在同一模式中拥有一个名为 EMP 的表

So, I tried.

所以，我试过了。

create synonym emp for scott.emp

It shows some error object already exist

它显示一些错误 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 ?

而且，得到了同样的错误。所以，任何人都请分享一些关于同义词的知识。我们可以在相同的模式中创建同名的同义词吗？

回答by Guneli

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:

首先要注意的是，公共同义词是非模式对象，而私有同义词和表是。另一个是数据库对象名称的唯一性由命名空间定义。正如 SQL 专家学习指南中所述：

• 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.

• USER、ROLE 和 PUBLIC SYNONYM 对象位于它们自己的集体命名空间中。
• TABLE、VIEW、SEQUENCE、PRIVATE SYNONYM 和用户定义的 TYPE 对象在给定模式中都有自己唯一的命名空间。
• INDEX 对象在给定模式中拥有自己的命名空间。
• CONSTRAINT 对象在给定模式中具有自己的命名空间。

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

因此，只要对象不共享相同的命名空间，您就可以为它们指定相同的名称。希望这可以帮助。

回答by user4348509

Public synonyms are non-schema objects, when private synonyms as tables are schema objects.

当作为表的私有同义词是模式对象时，公共同义词是非模式对象。

• USER, ROLEand PUBLIC SYNONYMare in their own collective namespace.
• TABLE, VIEW, SEQUENCE, PRIVATESYNONYMhave their own unique namespace.
• An INDEXhas their own unique namespace.
• A CONSTRAINTobject has their own unique namespace within a given schema

• USER，ROLE并且PUBLIC SYNONYM在他们自己的集体命名空间中。
• TABLE, VIEW, SEQUENCE,PRIVATESYNONYM有自己独特的命名空间。
• AnINDEX有自己独特的命名空间。
• 一个CONSTRAINT对象有一个给定的模式中自己独特的命名空间

While the objects don't share the same namespace, you can give them the same names.

虽然对象不共享相同的命名空间，但您可以为它们指定相同的名称。

Remember, that you can have:

请记住，您可以拥有：

• a table and public synonym with the same name
• a public synonym and a private synonym with same name

• 同名的表和公共同义词
• 具有相同名称的公共同义词和私有同义词

You cannot have:

你不能有：

• a table and a private synonym with the same name inside the same schema.

• 一个表和一个同名的私有同义词在同一个模式中。