oracle 如何为oracle db链接提取ddl？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32768569/
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 extract ddl for oracle db links?
提问by homer
Is there anyway to extract ddl for all database links? I would like to get in sql and recreate them via sql.
无论如何要为所有数据库链接提取ddl？我想进入 sql 并通过 sql 重新创建它们。
I can use below and it works for PUBLIC user but for non public user it doesn't give me db link owner.
我可以在下面使用，它适用于 PUBLIC 用户，但对于非公共用户，它不会给我 db 链接所有者。
Set long 1000
SELECT DBMS_METADATA.GET_DDL('DB_LINK',db.db_link,db.owner) from dba_db_links db;
Sample link owner and name
Owner db_link
public link1
public link2
user1 link3
If I ran above select it will give me below, #3 doesn't have username in it.
如果我在 select 上面运行，它会在下面给我，#3 中没有用户名。
Output from above SELECT
1. create public database link "link1" using "db_alias"
2. create public database link "link2" using "db_alias"
3. create database link "link3" using "db_alias"
I recreate links using SYS and don't want to create #3 as SYS user.
我使用 SYS 重新创建链接，并且不想以 SYS 用户身份创建 #3。
回答by Anton Zaviriukhin
Seems that even as SYS user you can't easity create dblink for another user (except of public dblink).
Even if you run create database link your_user.link3 using "db_alias"it's owner will be SYS.
Possible hacks are connect as another user
(you may add conninto SQL*Plus script if you have credentials)
Or create procedure for user that need to have dblink that run create database linkcommand with parameters and call it from sys.
似乎即使作为 SYS 用户，您也无法轻松地为其他用户创建 dblink（公共 dblink 除外）。
即使你运行create database link your_user.link3 using "db_alias"它的所有者也会是SYS。可能的黑客是作为另一个用户连接
（conn如果你有凭据，你可以添加到 SQL*Plus 脚本中）
或者为需要有 dblink 的用户创建程序来运行create database link带参数的命令并从 sys.path 调用它。
回答by sbj
this should help with links that do a "connect to"
这应该有助于“连接到”的链接
SELECT MYCOMMAND
FROM
(
select trim(l.owner)||'__'||trim(l.db_link)||'__'||trim(l.username) ||'__'||trim(l.host) , '10'
, ' connect '||trim(l.owner)||'/CCCCCCCC@'||TRIM(INSTANCE_NAME)
AS MYCOMMAND
from dba_db_links l , V$INSTANCE i , dba_users u
where l.username = u.username
and l.username is not null
and l.username <> ' '
UNION ALL
select trim(l.owner)||'__'||trim(l.db_link)||'__'||trim(l.username)||'__'||trim(l.host) , '20'
, ' DROP DATABASE LINK '||trim(l.db_link)||' ; ' AS MYCOMMAND
from dba_db_links l , V$INSTANCE i , dba_users u
where l.username = u.username
and l.username is not null
and l.username <> ' '
UNION ALL
select trim(l.owner)||'__'||trim(l.db_link)||'__'||trim(l.username)||'__'||trim(l.host) , '30'
, ' CREATE DATABASE LINK '||trim(l.db_link)
||' CONNECT TO "'||trim(l.username)||'"'
||' IDENTIFIED BY "NNNNNNNN" '
||' USING '||trim(l.host) ||' ; 'AS MYCOMMAND
from dba_db_links l , V$INSTANCE i , dba_users u
where l.username = u.username
and l.username is not null
and l.username <> ' '
ORDER BY 1,2,3
)
回答by Silvano Junior
To get ddl from db links, run the output of the command below:
要从 db 链接获取 ddl，请运行以下命令的输出：
set pages 999; set long 90000;
设置页面 999；设置多头 90000；
SELECT 'SELECT dbms_metadata.get_ddl(''DB_LINK'',''' || db_link || ''',''' || owner || ''') FROM dual;'
FROM dba_db_links
WHERE db_link
IN ('DB_LINK1',
'DB_LINK2',
'DB_LINK3');
For create a db link for other user, with user SYS execute the DDL below:
要为其他用户创建数据库链接，请使用 SYS 用户执行以下 DDL：
CREATE DATABASE LINK "OWNER.DBLINK_NAME"
CONNECT TO "USER" IDENTIFIED BY "Password_user"
USING 'ALIAS';
