如何在 Oracle 中使用 DBlink 的同义词？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34017232/
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
How to use synonym of a DBlink in Oracle?
提问by Nitish
I have created a synonym for a dblink.
我为 dblink 创建了同义词。
create synonym dblink2 for dblink1
But when I query anything using the synonym instead of the dblink, I'm getting connection description for remote database not founderror.
但是当我使用同义词而不是 dblink 查询任何内容时，我收到了远程数据库未找到错误的连接描述。
SELECT * FROM DUAL@DBLINK2
How do I query using the synonym?
Edit:I know that it'll work if I create a view of the table using dblink. But my requirement is the above question.
如何使用同义词查询？
编辑：我知道如果我使用 dblink 创建表的视图它会起作用。但我的要求是上述问题。
回答by Gergely Bacso
Unfortunately creation of synonyms for dblinks is not supported. If you read the documentation on synonyms, you will find that the permitted objects for synonyms are only:
不幸的是，不支持为 dblinks 创建同义词。如果您阅读有关同义词的文档，您会发现同义词的允许对象仅为：
Use the CREATE SYNONYM statement to create a synonym, which is an alternative name for a table, view, sequence, procedure, stored function, package, materialized view, Java class schema object, user-defined object type, or another synonym.
使用 CREATE SYNONYM 语句创建同义词，它是表、视图、序列、过程、存储函数、包、物化视图、Java 类模式对象、用户定义的对象类型或其他同义词的替代名称。
The reason why your second query fails is that the synomym you have created is not functioning correctly.It is not being validated properly at creation time, and you can create any sort of incorrect synonyms like that. To verify, just test the following statement:
您的第二个查询失败的原因是您创建的同义词没有正常运行。它在创建时没有得到正确验证，您可以创建任何类型的不正确的同义词。要验证，只需测试以下语句：
create synonym dblink3 for no_object_with_this_name;
You will still get a response like this:
你仍然会得到这样的回应：
*Synonym DBLINK3 created.*
But of course nothing will work via this synonym.
但当然，通过这个同义词没有任何作用。
回答by Lalit Kumar B
I don't see the point in creating a synonym for the dblink itself. Ideally you create the synonymfor the remote tableusing the dblink.
我认为为 dblink 本身创建同义词没有意义。理想情况下，您可以使用dblink为远程表创建同义词。
CREATE DATABASE LINK my_db_link CONNECT TO user IDENTIFIED BY passwd USING 'alias';
CREATE SYNONYM my_table FOR remote_table@my_db_link;
Now, you could query the remote tableusing the synonym:
现在，您可以使用同义词查询远程表：
SELECT * FROM my_table;
回答by Michael Broughton
I'm trying to think of the business issue that gets solved by putting a synonym on a db_link, and the only thing I can think of is that you need to deploy constant code that will be selecting from some_Table@some_dblink, and although the table names are constant different users may be looking across different db_links. Or you just want to be able to swap which db_link you are operating across with a simple synonym repoint.
我正在尝试考虑通过在 db_link 上放置同义词来解决的业务问题，我唯一能想到的是您需要部署将从 some_Table@some_dblink 中选择的常量代码，尽管表名称是不变的，不同的用户可能会查看不同的 db_links。或者您只是希望能够使用简单的同义词重新指向来交换您正在操作的 db_link。
Here's the problem: it can't be done that way. db_link synonyms are not allowed.
问题是：不能那样做。db_link 同义词是不允许的。
Your only solution is to have the code instead reference the tables by synonyms, and set private synonyms to point across the correct db_link. That way your code continues to "Select from REMOTE_TABLE1" and you just can flip which DB_LINK you are getting that remote table from.
您唯一的解决方案是让代码通过同义词引用表，并将私有同义词设置为指向正确的 db_link。这样你的代码继续“从 REMOTE_TABLE1 中选择”，你就可以翻转你从哪个 DB_LINK 获取远程表。
Is it a pain to have to set/reset 100+ private synonyms? Yep. But if it is something you need to do often then bundle up a procedure to do it for you where you pass in the db_link name and it cycles through and resets the synonyms for you.
必须设置/重置 100 多个私有同义词是否很痛苦？是的。但是如果这是你需要经常做的事情，那么捆绑一个过程来为你做，你传入 db_link 名称，它会循环并为你重置同义词。
回答by Peter T.
While I understand that this question is 3+ years old, someone might be able to benefit from a different answer in the future.
虽然我知道这个问题已经有 3 年以上的历史了，但将来有人可能会从不同的答案中受益。
Let's imagine that I have 4 databases, 2 for production and 2 for dev / testing.
假设我有 4 个数据库，2 个用于生产，2 个用于开发/测试。
Prod DBs: PRDAPP1DB1 and PRDAPP2DB1 Dev DBs: DEVAPP1DB1 and DEVAPP2DB1
生产数据库：PRDAPP1DB1 和 PRDAPP2DB1 开发数据库：DEVAPP1DB1 和 DEVAPP2DB1
The "APP2" databases are running procedures to extract and import data from the APP1 databases. In these procedures, there are various select statements, such as:
“APP2”数据库正在运行从APP1数据库中提取和导入数据的程序。在这些过程中，有各种选择语句，例如：
declare
iCount INTEGER;
begin
insert into tbl_impdata1
select sysdate, col1, col2, substr(col3,1,10), substr(col3,15,3)
from tbl1@dblink2; -- Where dblink2 points to DEVAPP1DB1
...
<more statements here>
...
EXCEPTION
<exception handling code here>
end;
Now that is okay for development but the dblink2 constantly needs to be changed to dblink1 when deploying the updated procedure to production.
现在这对开发来说没问题，但是在将更新的过程部署到生产时，dblink2 经常需要更改为 dblink1。
As it was pointed out, synonyms cannot be used for this purpose. But instead, create the db links with the same name, different connection string.
正如所指出的，同义词不能用于此目的。但相反，创建具有相同名称、不同连接字符串的数据库链接。
E.g. on production:
例如在生产上：
CREATE DATABASE LINK "MyDBLINK" USING 'PRDAPP1DB1';
And on dev:
在开发上：
CREATE DATABASE LINK "MyDBLINK" USING 'DEVAPP1DB1';
And then in the procedures, change all "@dblink1" and "@dblink2" to "@mydblink" and it all should be transparent from there.
然后在程序中，将所有“@dblink1”和“@dblink2”更改为“@mydblink”，并且从那里所有这些都应该是透明的。
回答by Olafur Tryggvason
If you are trying to have the DB link accessible for multiple schemas (users) the answer is to create a publicdb link
如果您尝试让多个模式（用户）可以访问数据库链接，则答案是创建一个公共数据库链接
example:
例子：
CREATE PUBLIC DATABASE LINK dblink1 CONNECT TO user IDENTIFIED BY password USING 'tnsalias';
After that any schema can issue a:
之后，任何模式都可以发出：
SELECT * FROM TABLE@dblink1
