Oracle：是否可以为模式创建同义词？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3780046/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle: is it possible to create a synonym for a schema?
提问by AJ.
Firstly
首先
I am an oracle newbie, and I don't have a local oracle guru to help me.
我是一个 oracle 新手，我没有当地的 oracle 大师来帮助我。
Here is my problem / question
这是我的问题/问题
I have some SQL scripts which have to be released to a number of Oracle instances.
The scripts create stored procedures.
The schema in which the stored procedures are created is different from the schema which contains the tables from which the stored procedures are reading.
我有一些 SQL 脚本必须发布到许多 Oracle 实例。脚本创建存储过程。
创建存储过程的架构与包含存储过程从中读取的表的架构不同。
On the different instances, the schema containing the tables has different names.
在不同的实例上，包含表的架构具有不同的名称。
Obviously, I do not want to have to edit the scripts to make them bespoke for different instances.
显然，我不想编辑脚本来为不同的实例定制它们。
It has been suggested to me that the solution may be to set up synonyms.
有人向我建议，解决方案可能是设置同义词。
Is it possible to define a synonym for the table schema on each instance, and use the synonym in my scripts?
是否可以为每个实例上的表模式定义同义词，并在我的脚本中使用同义词？
Are there any other ways to make this work without editing the scripts every time?
有没有其他方法可以使这项工作无需每次都编辑脚本？
Thank you for any help.
感谢您的任何帮助。
采纳答案by OMG Ponies
It'd help to know what version of Oracle, but as of 10g--No, you can't make a synonym for a schema.
You can create synonyms for the tables, which would allow you not to specify the schema in the scripts. But it means that the synonyms have to be identical on every instance to be of any use...
知道什么版本的 Oracle 会有所帮助，但从10g 开始——不，你不能为 schema 做同义词。
您可以为表创建同义词，这将允许您不在脚本中指定架构。但这意味着同义词必须在每个实例上都相同才能有用......
The other option would be to replace the schema references with variables, so when the script runs the user is prompted for the schema names. I prefer this approach, because it's less work. Here's an example that would work in SQLPlus:
另一种选择是用变量替换模式引用，因此当脚本运行时，用户会被提示输入模式名称。我更喜欢这种方法，因为它的工作量较少。这是一个适用于 SQLPlus 的示例：
CREATE OR REPLACE &schema1..vw_my_view AS
SELECT *
FROM &&schema2..some_other_table
The beauty of this is that the person who runs the script would only be prompted once for each variable, not every time the variable is encountered. So be careful about typos :)
这样做的好处在于，对于每个变量，运行脚本的人只会被提示一次，而不是每次遇到变量时都会提示。所以要小心打字错误:)
回答by Dmitry
Yes, there is a hidden way to create a schema synonym.
是的，有一种隐藏的方式来创建模式同义词。
There is a hidden parameter _enable_schema_synonyms. It's false by default , but you can set it to true and create a synonym.
有一个隐藏参数_enable_schema_synonyms。默认为 false ，但您可以将其设置为 true 并创建同义词。
回答by rajkishore patro
Yes, you can create synonym for a schema.
是的，您可以为架构创建同义词。
select ksppinm, ksppstvl from x$ksppi a, x$ksppsv b where a.indx=b.indx and ksppinm like '%schema%synonym%'
ALTER SYSTEM SET "_enable_schema_synonyms" = true SCOPE=SPFILE;
STARTUP FORCE
show parameter synonym
Assuming you already have a schema named ORA...
假设您已经有一个名为 ORA 的模式...
CREATE SCHEMA SYNONYM ORASYN for ORA; -- create synonym for schema
CREATE TABLE ORASYN.TAB1(id number(10)); -- create table in schema
More information here: http://oracle-network.com/database/how-to-create-synonym-for-a-schema/
更多信息：http: //oracle-network.com/database/how-to-create-synonym-for-a-schema/
