oracle 如何创建远程和本地是同一台服务器的数据库链接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2960594/
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 do I create a Database Link where remote and local are the same server
提问by Brian G
I have a need to create a database link that is a link to a schema on the same server. Is there a special keyword to use for this? ( like local or localhost )
我需要创建一个数据库链接，该链接是指向同一服务器上架构的链接。是否有特殊的关键字可以用于此目的？（如本地或本地主机）
I am not entirely sure what the name of the server is in tnsnames and that may be part of my problem.
我不完全确定 tnsnames 中服务器的名称是什么，这可能是我的问题的一部分。
This is for a complicated situation which involves a script that needs a database link to continue.
这适用于涉及需要数据库链接才能继续的脚本的复杂情况。
回答by DCookie
The DB link mechanism goes through TNS, so just define a TNS entry for your local database and use that in your link.
DB 链接机制通过 TNS，因此只需为您的本地数据库定义一个 TNS 条目并在您的链接中使用它。
Your client TNSNAMES.ORA files should contain an entry that looks something like:
您的客户端 TNSNAMES.ORA 文件应包含类似于以下内容的条目：
YourDBAlias =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = yourHOST)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = YourDB)
)
)
Make sure this entry also exists in the TNSNAMES.ORA file on your database server.
确保该条目也存在于您的数据库服务器上的 TNSNAMES.ORA 文件中。
Then, create the database link as:
然后，创建数据库链接：
CREATE [PUBLIC] DATABASE LINK yourLinkName
CONNECT TO theSchema IDENTIFIED BY thePW
USING 'YourDBAlias';
This should do the trick (assuming you're using TNS naming).
这应该可以解决问题（假设您使用的是 TNS 命名）。
Additionally, if you're not sure what your TNS Name is for the database, in SQL*Plus you can issue the command:
此外，如果您不确定数据库的 TNS 名称是什么，您可以在 SQL*Plus 中发出以下命令：
show parameter service_names
This will show you the name(s) that the database is registered with it's listener as. You should be able to find the corresponding entry in the TNSNAMES.ORA file from that.
这将向您显示数据库在其侦听器中注册的名称。您应该能够从中找到 TNSNAMES.ORA 文件中的相应条目。
回答by Gary Myers
If you can't amend TNSNAMES.ORA you can use the Easy Connect syntaxeven for DB Links. Assuming the listener is on the default port, then the following SQL will get the conneection string
如果您不能修改 TNSNAMES.ORA，您甚至可以对数据库链接使用Easy Connect 语法。假设监听器在默认端口，那么下面的SQL会得到连接字符串
select utl_inaddr.get_host_address||':1521/'||sys_context('USERENV','INSTANCE_NAME') from dual
从双重选择 utl_inaddr.get_host_address||':1521/'||sys_context('USERENV','INSTANCE_NAME')
回答by dpbradley
You may have a problem if by "schema on the same server" you mean a schema in the same database. (For example, if the script was treating anything outside of the schema as an external database to flatten subsequent SQL operations).
如果“同一服务器上的架构”指的是同一数据库中的架构，您可能会遇到问题。（例如，如果脚本将架构之外的任何内容视为外部数据库以扁平化后续 SQL 操作）。
Oracle treats loopback links somewhat differently, and you may receive ORA-02082 errors ("a loopback database link must have a connection qualifier") if using the database global name for the link. If you receive this error, you have to name the link something different, like "loopback", but this also requires that the global_names database parameter is set to false. Otherwise you'll receive "ORA-02085: database link someName connects to someOtherName"
Oracle 对环回链接的处理方式略有不同，如果使用链接的数据库全局名称，您可能会收到 ORA-02082 错误（“环回数据库链接必须具有连接限定符”）。如果收到此错误，则必须将链接命名为不同的名称，例如“loopback”，但这也要求将 global_names 数据库参数设置为 false。否则，您将收到“ORA-02085：数据库链接 someName 连接到 someOtherName”
