How to extract ddl for oracle db links?
Is there anyway to extract ddl for all database links? I would like to get in sql and recreate them via sql.
I can use below and it works for PUBLIC user but for non public user it doesn't give me db link owner.
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.
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.
Answer
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 conn into SQL*Plus script if you have credentials)
Or create procedure for user that need to have dblink that run create database link command with parameters and call it from sys.