用于创建另一个用户拥有的数据库链接的 Oracle 语法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/988230/
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 Syntax for Creating Database Link Owned by Another User
提问by Jeff
The typical syntax for creating a db link is as follows:
创建数据库链接的典型语法如下：
create database link remote_db_link
connect to remote_user
identified by remote_password
using 'remote_db'
But I'd like my DB link owned by another account after it's created. Is there a way to do this?
但是我希望我的数据库链接在创建后归另一个帐户所有。有没有办法做到这一点？
The following does NOT work:
以下不起作用：
create database link anotheruser.remote_db_link
connect to remote_user
identified by remote_password
using 'remote_db'
回答by spencer7593
Sathya is correct, in that the CREATE DATABASE LINKsyntax does not allow creating a database link in another schema. HOWEVER...
Sathya 是正确的，因为CREATE DATABASE LINK语法不允许在另一个模式中创建数据库链接。然而...
WORKAROUND
变通方法
It ISpossible to create a database link in another user's schema, as long as anotheruserhas CREATE DATABASE LINKprivilege, and the user you are connected as has CREATE ANY PROCEDUREprivilege.
它IS可以创建其他用户的模式的数据库链接，只要anotheruser有CREATE DATABASE LINK特权，您已连接用户拥有CREATE ANY PROCEDURE的特权。
Here's the workaround I use:
这是我使用的解决方法：
create procedure anotheruser."tmp_doit_200906121431"
is
begin
execute immediate '
create database link remote_db_link
connect to remote_user
identified by remote_password
using ''remote_db'' ';
end;
/
begin
anotheruser."tmp_doit_200906121431";
end;
/
drop procedure anotheruser."tmp_doit_200906121431"
/
Let's unwind that. First, I create a procedure in the anotherusers's schema; this procedure contains the CREATE DATABASE LINKstatement that I want to run.
让我们放松一下。首先，我在anotherusers的架构中创建一个过程；此过程包含CREATE DATABASE LINK我要运行的语句。
When the procedure is executed, it runs as the owner of the procedure, such that the CREATE DATABASE LINKstatement is executed by anotheruser.
执行过程时，它作为过程的所有者运行，因此CREATE DATABASE LINK语句由 执行anotheruser。
The name of the procedure is not important, except that I need to make sure that it doesn't conflict with any existing object name. I use lowercase letters (enclosing the procedure name in double quotes), using "tmp" to mark this object as "temporary", and using the current yyyymmddhh24miss as the part of the procedure name. (I usually run a query of DBA_OBJECTS to check that a matching object_name does not exist.)
过程的名称并不重要，只是我需要确保它不与任何现有的对象名称冲突。我使用小写字母（将过程名称用双引号括起来），使用“tmp”将此对象标记为“临时”，并使用当前的 yyyymmddhh24miss 作为过程名称的一部分。（我通常运行 DBA_OBJECTS 查询来检查匹配的 object_name 是否不存在。）
For a "one-off" type admin function, this is a viable workaround. I prefer this to the other alternative: saving the anotheruser's password, changing the password, connecting as the user, and resetting anotheruser's password back to the saved.)
对于“一次性”类型的管理功能，这是一种可行的解决方法。与其他选择相比，我更喜欢这个：保存另一个用户的密码，更改密码，以用户身份连接，并将另一个用户的密码重置回保存的密码。）
回答by Sathyajith Bhat
Restrictions on DBLinks- You cannot create a database link in another user's schema, and you cannot qualify dblink with the name of a schema.
DBLink 的限制- 您不能在另一个用户的架构中创建数据库链接，并且您不能使用架构名称限定 dblink。
回答by Polu
AS a sys user you can view all db links in SYS.DBA_DB_LINKS view. That view use link$ and user$ table. You can create new dblink as usually and it show at link$ table. Then change owner (use id from user$). commit. Done.
作为 sys 用户，您可以在 SYS.DBA_DB_LINKS 视图中查看所有数据库链接。该视图使用 link$ 和 user$ 表。您可以像往常一样创建新的 dblink，它会显示在 link$ 表中。然后更改所有者（使用 user$ 中的 id）。犯罪。完毕。
